Automated DB Restore Based on SQL Backup FileName

pcorujo

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

petey

The auto generated name will be stored in 2 locations on the backup server, in the msdb..backupmediafamily table, and in the SQL Backup local data base.

E.g. to retrieve the latest full backup of the 'pubs' database from the msdb..backupmediafamily table:

SELECT a.physical_device_name 
FROM msdb..backupmediafamily a
WHERE a.media_set_id = 
(SELECT TOP 1 media_set_id 
 FROM msdb..backupset 
 WHERE database_name = 'pubs'
   AND type = 'D'
 ORDER BY backup_finish_date DESC)

Similarly from the SQL Backup local data base:

EXEC master..sqbdata 'SELECT name FROM backupfiles WHERE backup_id IN (SELECT MAX(ID) FROM backuphistory WHERE backup_type = ''D'' AND dbname = ''pubs'')'

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.

EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs FROM DISK = [g:\backups\pubs\*FULL*.sqb] LATEST_FULL WITH REPLACE"'

pcorujo

Thank you sir, thats perfect.