Naming Backup when saving to Disk with loop

Question

Naming Backup when saving to Disk with loop

Deny Christian

SSCrazy

Points: 2297
More actions
October 16, 2016 at 10:19 pm

#322117

Dear all expert,
kindly please help on this syntax, I tried to make database name at filed description/name at msdb.dbo.backupset by looping
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'D:\Backup\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(8),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name = 'AdventureWorksDW2008R2'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH INIT, name = N''full backup database' + @name' --> It always error, i've tried many times to modify the syntax and have no solution for this
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

The BACKUP DATABASE statement cannot be made to use variables directly. You must build it as dynamic SQL and then execute it.

Sad but true. They didn't make it easy on us.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

x SSC-Insane Points: 23660 More actions · Answer 2

Jeff Moden - Sunday, October 16, 2016 11:09 PM
The BACKUP DATABASE statement cannot be made to use variables directly. You must build it as dynamic SQL and then execute it.Sad but true. They didn't make it easy on us.

I think its more along the lines of parameter limits like stored procedures. You cannot put expressions directly in line with the statement (in this case, concatenation in the "naming" of the backup), but simple variables seem to work ok as long as they're standalone variables and not full on expressions. Stored procedure parameters act the same way. So do all your concatenation and save into variables BEFORE the backup statement.

declare @db varchar(100) declare @dir varchar(100) declare @dbname varchar(100)

set @db = 'testdb'
set @dir = 'd:\testdir\testdb.bak'
set @dbname = 'testdb'
BACKUP DATABASE @db TO DISK = @Dir with init, NAME = @dbname