Date and Time in a name of manual backup SQLEXPRESS.

Question

sql.sql

DECLARE @pathName NVARCHAR(512) SET @pathName = 'l:\Backup\db_backup_' + Convert(varchar(17), GETDATE(), 120) + '.bak' BACKUP DATABASE mydatabase TO DISK = @pathName WITH NOFORMAT, NOINIT, NAME = N'db_backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

backup.bat

sqlcmd -S mycomp\SQLEXPRESS -U sa -P password -i sql.sql

Output backup format look like : l:\Backup\db_backup_2014-05-22 17 ?? Why are not displayed Minutes,seconds and extensions ".bak" . I need to displayed date and time in a format : YYYY-MM-DD HH-MM-SS. Thanks for Answers.

Answer 1

You're not getting seconds because this statement:

Convert(varchar(17), GETDATE(), 120)

Stops the string at 17 characters, which isn't long enough to include all the data you want. Try

Convert(varchar(19), GETDATE(), 120)

Instead.

As for the fact that your filename is being truncated, this is probably because it contains a colon, which is illegal in file paths and names. Try this.

Replace(Convert(varchar(19), GETDATE(), 120), ':', '_')

Altogether, your SQL will need to be:

DECLARE @pathName NVARCHAR(512)

SET @pathName = 'l:\Backup\db_backup_' + REPLACE(CONVERT(VARCHAR(19), GETDATE(), 120), ':', '_') + '.bak'

BACKUP DATABASE mydatabase TO DISK = @pathName
WITH NOFORMAT
    ,NOINIT
    ,NAME = N'db_backup'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10