Automated DB Restore Based on SQL Backup FileName
Hi All,
I am backing up a database, and using the copy option to move it to another server. I then need to automate the restore of that backup on the new server. The backup naming convention is set to auto. Is there anyway to get that auto generated name so I can automate the restore?
Thank you all in advance,
Paul
I am backing up a database, and using the copy option to move it to another server. I then need to automate the restore of that backup on the new server. The backup naming convention is set to auto. Is there anyway to get that auto generated name so I can automate the restore?
Thank you all in advance,
Paul
Comments
E.g. to retrieve the latest full backup of the 'pubs' database from the msdb..backupmediafamily table:
Similarly from the SQL Backup local data base:
Another option would be to parse the output from the backup process. You could do this by running the backup with the SINGLERESULTSET option and store the results in a temporary table, and thereafter retrieve the 2nd line of the output which will be the generated file name.
Yet another option is available if you just want to restore the latest available backup set. If you are restoring a full database backup, you could use the LATEST_FULL option e.g.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8