May 2, 2019 at 10:06 am
Hi all
Was not sure where to post this, so apologies if its not in the right area.
I'm trying to create a job which will restore databases using Redgate.
If I execute this, I get an error, but if I just print the @SQL, then paste it after an Exec sqlpbackup, it works. I have to add that my filename has underscores and parenthesis in it (sigh), but I have no control over that
SELECT @SQL = '-SQL "RESTORE DATABASE ' + DatabaseName + ' FROM DISK = ''''G:\Backup\TestDB\' + Filename + ''''' WITH NORECOVERY, REPLACE "'
,@Restore_ID = RestoreID
FROM OH_Test.[dbo].[Restore_Files]
WHERE DatabaseName LIKE 'MyDb'
AND ToBeRestored = 'Y'
AND CHARINDEX('FULL', Filename) > 0
EXECUTE master..sqlbackup @SQL
Error returned is Syntax error: 'G:\Backup\TestDB\FULL_(local)_MyDB_20190427_150000.sqb''' after ''
So, I think its to do with the quotation marks. But that doesn't really answer why my print statement, when used, works.
Print statement returns:
-SQL "RESTORE DATABASE MyDB FROM DISK = ' 'G:\Backup\FULL_(local)_MyDB_20190427_150000.sqb' ' WITH NORECOVERY, REPLACE "
Which then I can execute as
EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE MyDB FROM DISK = ' 'G:\Backup\FULL_(local)_MyDB_20190427_150000.sqb' ' WITH NORECOVERY, REPLACE "'
I hope that makes sense
thank you
May 2, 2019 at 10:50 am
Out of interest, why not use the GUI? Looks like your statement should be this though:
SELECT @SQL = '-SQL ''RESTORE DATABASE ' + DatabaseName + ' FROM DISK = ''G:\Backup\TestDB\' + Filename + ''' WITH NORECOVERY, REPLACE "' ...
In your PRINT statement you have '...FROM DISK = ' 'G:\Backup\FULL...' ' which isn't valid, you've open and closed the quotes before providing your value for DISK.
Edit: Side note, you can find support on the Red Gate products here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply