scripting restore job

dwjongbloed

I am trying to script a restore job on my Disaster recovery server and have the script just about done but am running into a snag. When I check the syntax on it I get this error:Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'master'

Here is the script:
declare @filename varchar(100)
declare @todaydate varchar(13)
set @todaydate=datename(weekday,getdate())+'.sqb'
set @filename = 'd:\program files\microsoft sql server\mssql\backup\'+@todaydate
master..sqlbackup N'-SQL "RESTORE DATABASE [XXXXX]
FROM DISK = @filename WITH RECOVERY, REPLACE"'


Any suggestions on this would be greatly appreciated.

priyasinha

Hi,

You are getting syntax error because filename is not being passed correctly in the command. For the syntax to work .. it should pass it as DISK = ''c:\temp\mybackup.sqb'' which is not happening in the script you have mentioned.

So you can use the below command instead:

declare @filename varchar(100)
declare @todaydate varchar(13)
declare @sql varchar(4000)
set @todaydate=datename(weekday,getdate())+'.sqb'
set @filename = 'c:\temp\'+@todaydate
set @filename = '''' + '''' + @filename + '''' + ''''
set @sql = REPLACE('execute master..sqlbackup ''-SQL "Restore DATABASE [xx] FROM DISK = {0} WITH RECOVERY, REPLACE"''', '{0}' ,@filename)
EXEC(@sql)

Hope this helps.

Regards,
Priya