How can we help you today? How can we help you today?

scripting restore job

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.
dwjongbloed
0

Comments

1 comment

Sort by
  • 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
    priyasinha
    0

Add comment

Please sign in to leave a comment.