Possible to use a variable in the restore script?

terrihawkins

I have a VB script which goes through a folder and finds the most current SQL backup of a database and assigns that name to sNewestFile. I want to do an automatic restore everyday of the most current file to another environment, and was hoping to use this variable to tell SQLBackup what to restore... Is that possible?

EXECUTE master..sqlbackup '-SQL "RESTORE DATABASE [table_backup] FROM DISK = ''M:\SQL Backups\test_folder\FULL_table_backup_20110104_085509.sqb'' WITH RECOVERY, DISCONNECT_EXISTING, REPLACE, ORPHAN_CHECK"'

Can I replace that with the variable name? Is there something special I have to do to accomplish this? Am I missing a much better way to do this? I am using the default backup format with the name and date, so I could not see how to just use the restore script as it stands.

Any input is appreciated.

petey

If you're using SQL Backup 6.3 or newer, try this:

EXEC master..sqlbackup '-sql "RESTORE DATABASE [table_backup] FROM DISK = [M:\SQL Backups\test_folder\FULL_table_backup_*.sqb] LATEST_FULL WITH RECOVERY, DISCONNECT_EXISTING, REPLACE" '

If you're using 6.2 or older, try this:

DECLARE @cmd NVARCHAR(1024) 
DECLARE @dbfilename NVARCHAR(256) 

SET @dbfilename = '<your backup file name>' 

SET @cmd = '-sql "RESTORE DATABASE [table_backup] FROM DISK = [' + @dbfilename + '] WITH RECOVERY, DISCONNECT_EXISTING, REPLACE"'

EXEC master..sqlbackup @cmd

terrihawkins

Wow! Thanks, that's a MUCH better way than what I was doing!