Syntax question

tstadler

Well, this will sound a little stupid but I don't usualy handle scripting but I will ask anyway.
I am reworking some scripts that do automated restores here, they pass in a variable to a stored proc
A standard restore is
exec master..sqlbackup N'-SQL "RESTORE DATABASE [QAT] FROM DISK = ''\\test\test\test.sqb'' WITH RECOVERY"'
I want to pass in a variable like @FilePath,
exec master..sqlbackup N'-SQL "RESTORE DATABASE [QAT] FROM DISK = ''@FilePath'' WITH RECOVERY"'
Wondered if you can provide me some direction how I can do a variable subsutition instead of the actual path.
Thanks

petey

The last post in this topic (http://www.red-gate.com/messageboard/viewtopic.php?t=1666) may offer some hints.

Brian Donahue

Hello Tom,

You can concatenate strings using the + operator in SQL. The other trick is to remember to escape your quotes so the SQL Server doesn't mistake your variable as a string literal:

SET @path='\\test\test\'+SELECT CAST(GETDATE() AS varchar(50))+'.SQB'
exec master..sqlbackup N'-SQL "RESTORE DATABASE [QAT] FROM DISK = '''+@path+''' WITH RECOVERY"'