Possible to use a variable in the restore script?

terrihawkinsterrihawkins Posts: 17
edited January 4, 2011 10:51AM in SQL Backup Previous Versions
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.
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • terrihawkinsterrihawkins Posts: 17
    Wow! Thanks, that's a MUCH better way than what I was doing!
    · Share on Twitter
Sign In or Register to comment.