Problem Using a Dynamic Filename for a Database Restore

Question

Problem Using a Dynamic Filename for a Database Restore

Cory Blythe

SSC Eights!

Points: 946
More actions
April 14, 2010 at 7:29 am

#213800

Hi,
I have a feeling I've just been staring at this too long and the solution is obvious, so any help is greatly appreciated.
I'm trying to use the code below to restore a backup of our production database on a nightly basis to a development database. Each night the backup file name changes with the date appended to the backup file name _yyyymmdd0000.bak.
The string generated by @cmdstr works if I drop it into a new query window and execute it, however when I execute the SP I receive the following error:
Msg 203, Level 16, State 2, Procedure RestoreREOTest, Line 32
The name 'RESTORE DATABASE [REOTest] FROM DISK = N'G:\Database Backups\REO\REO_backup_201004140000.bak' WITH FILE = 1, MOVE N'PAM' TO N'F:\DataFiles\REOTest.mdf', MOVE N'PAM_log' TO N'G:\LogFiles\REOTest_1.ldf', NOUNLOAD, REPLACE, STATS = 10' is not a valid identifier.
DECLARE @TodayDate AS NVARCHAR(10) ,
@TodayMonth AS NVARCHAR(10) ,
@TodayYear AS NVARCHAR(10) ,
@TodayHour AS NVARCHAR(10) ,
@TodayMinute AS NVARCHAR(10) ,
@TodaySecond AS NVARCHAR(10) ,
@NewFileName AS NVARCHAR(1000) ,
@cmdstr AS NVARCHAR(1000)
-- Select and Format Date/Time
SELECT @TodayDate=RIGHT('0'+DATEPART(dd, GETDATE()), 2)
SELECT @TodayDate=RIGHT('00', 2-LEN(@TodayDate))+@TodayDate
SELECT @TodayMonth=DATEPART(mm, GETDATE())
SELECT @TodayMonth=RIGHT('00', 2-LEN(@TodayMonth))+@TodayMonth
SELECT @TodayYear=RIGHT(DATEPART(yyyy, GETDATE()),4)
SET @NewFileName = 'G:\Database Backups\REO\REO_backup_' + @TodayYear + @TodayMonth + @TodayDate + '0000.bak'
--SELECT @NewFileName
SET @cmdstr = 'RESTORE DATABASE [REOTest] FROM DISK = N''' + @NewFileName + '''' + ' WITH FILE = 1, MOVE N'+'''PAM''' + ' TO N' + '''F:\DataFiles\REOTest.mdf''' + ', MOVE N'+'''PAM_log'''+ ' TO N' + '''G:\LogFiles\REOTest_1.ldf''' + ', NOUNLOAD, REPLACE, STATS = 10'
--SELECT @cmdstr
EXEC @cmdstr
Thanks for the help

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

If the new location (MOVE clauses in the RESTORE DATABASE) are fixed, you really don't need dynamic sql to accomplish the RESTORE. Check out the following code:

DECLARE @DBBackupFile as nvarchar(128);

SET @DBBackupFile = N'G:\Database Backups\REO\REO_backup_' + CONVERT(nvarchar(8), getdate(), 112) + N'0000.bak';

SELECT @DBBackupFile;

RESTORE DATABASE [REOTest]

FROM DISK = @DBBackupFile

WITH FILE = 1,

MOVE N'PAM' TO N'F:\DataFiles\REOTest.mdf',

MOVE N'PAM_log' TO N'G:\LogFiles\REOTest_1.ldf',

NOUNLOAD,

REPLACE,

STATS = 10;

Cory Blythe SSC Eights! Points: 946 More actions · Answer 2

This worked great. I'm afraid I got so focused on 1 way of doing things I didn't look for other alternative.

Thanks

Cory

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Cory Blythe (4/14/2010)
This worked great. I'm afraid I got so focused on 1 way of doing things I didn't look for other alternative.
Thanks
Cory

That's what makes forums like this so great, you get a fresh perspective on a problem that results in a solution that you may not have seen.

Thanks for the feedback, and I'm glad it works for you.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Lynn Pettis (4/14/2010)
If the new location (MOVE clauses in the RESTORE DATABASE) are fixed, you really don't need dynamic sql to accomplish the RESTORE.

Nice thinking Lynn

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

piyushvashishta SSC Rookie Points: 35 More actions · Answer 5

piyushvashishta

SSC Rookie

Points: 35

February 12, 2012 at 6:39 am

#1446032

Greate way and different alternative.....