Options
Executing using sql variable does not work
hughthomas
Posts: 7
I am banging my head against the wall (figuratively) trying to script transaction backups, and it is not working. I am concatenating together a variable to use as the single input variable to the extended stored procedure. If I print the statement to the screen and manually run it, it works perfectly. When I run it through the variable, I don't get an error but it doesn't execute the backup or restore. I even removed the concatenation and it still doesn't work. Below is the most simplified version.
I appreciate any help that you can offer.
ENVIRONMENT: Windows 2003 Server x64, SQL Server 2005 x64 SP2
SQL STATEMENT:
declare @sqls nvarchar(max)
Set @sqls = '-SQL "RESTORE LOG [MyDatabase] FROM DISK = [\\BKSERVER\SQLBACKUP\LOGSHIP\LOG_(local)_MyDatabase_*.sqb] WITH STANDBY = [\\BKSERVER\SQLBACKUP\UNDO_MyDatabase.DAT], ERASEFILES = 1, FILEOPTIONS = 1, MOVETO = [\\BKSERVER\SQLBACKUP\LOGSHIP\DONE]"'
print @sqls
exec master..sqlbackup @sqls
I appreciate any help that you can offer.
ENVIRONMENT: Windows 2003 Server x64, SQL Server 2005 x64 SP2
SQL STATEMENT:
declare @sqls nvarchar(max)
Set @sqls = '-SQL "RESTORE LOG [MyDatabase] FROM DISK = [\\BKSERVER\SQLBACKUP\LOGSHIP\LOG_(local)_MyDatabase_*.sqb] WITH STANDBY = [\\BKSERVER\SQLBACKUP\UNDO_MyDatabase.DAT], ERASEFILES = 1, FILEOPTIONS = 1, MOVETO = [\\BKSERVER\SQLBACKUP\LOGSHIP\DONE]"'
print @sqls
exec master..sqlbackup @sqls
Comments
I've tried the statement you have supplied and I'm not able to reproduce your error. However, there are a couple of things I would suggest trying that may help:
* Change nvarchar(max) to nvarchar(4000) - SQL Backup has a limit of 64,000 characters for the command, and while you shouldn't hit that limit, defining a max variable may cause issues.
* For your @sqls set statement, you should prefix it with an N for unicode, so that it's clear what you are executing.
Also, what output do you get when the command runs? Even if the command fails to do a backup or restore, you should see two grids below - the first window titled "SQL Backup v5.2.0.2807", and the second has two columns, one called "name" and one "value".
If you don't see any output, it would be useful to run a profiler trace when executing the script to establish what is actually happening behind the scenes... that may give more insight into where the problem lies.
Thanks,
Jason
I appreciate your help.
Hugh