Database backup using dynamic SQL

Question

I am creating a job which takes backup of a database and with the filename suffixing today's date. But I'm getting below error.

DECLARE @Query NVARCHAR(MAX)
SET @Query = 'BACKUP DATABASE [DBName] TO DISK = ''D:\Backups\DBName_'+cast(convert(date,getdate())as varchar)+'.bak'
EXEC sp_executeSql @Query

Error Message:

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'D:\Backups\DBName_2017-07-07.bak'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'D:\Backups\DBName_2017-07-07.bak'.

You have unmatched quotes. You probably need to escape them or use double quotes if that's an option. — Christopher Schneider, Jul 07 '17 at 16:47
Also prolly best not to name you backup with `.txt` (they are not textual backups, they are a binary format) — Alex K., Jul 07 '17 at 16:49
Also see [How to insert a value that contains an apostrophe (single quote)?](https://stackoverflow.com/questions/1912095/how-to-insert-a-value-that-contains-an-apostrophe-single-quote) — huMpty duMpty, Jul 07 '17 at 16:52

score 1 · Answer 1 · answered Jul 07 '17 at 16:49

You are missing the last closing quote. It should end in .txt'''

SET @Query = 'BACKUP DATABASE [MetaCost] TO DISK = ''D:\Backups\DBName.bak_'+cast(convert(date,getdate())as varchar)+'.txt'''

Also typically you use the .bak extension for a sql database backup.

score 0 · Answer 2 · answered Jul 07 '17 at 16:50

0

Your statement will output

BACKUP DATABASE [DBName] TO DISK = 'D:\Backups\DBName.bak_2017-07-07.txt

You need to add '.txt'''

answered Jul 07 '17 at 16:50

huMpty duMpty

14,346
14
60
99

Database backup using dynamic SQL

2 Answers2