What are the challenges you face when working across database platforms? Take the survey

Automated DB Restore Based on SQL Backup FileName

pcorujopcorujo Posts: 14
edited January 27, 2011 10:52AM in SQL Backup Previous Versions
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,


  • Options
    peteypetey Posts: 2,358 New member
    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"'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Thank you sir, thats perfect.
Sign In or Register to comment.