Automated DB Restore Based on SQL Backup FileName

Comments

• petey Redgate › Posts: 2,358 New member

  January 26, 2011 7:52PM

  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

  0 · Share on Twitter
• pcorujo Posts: 14

  January 27, 2011 10:52AM

  Thank you sir, thats perfect.

  0 · Share on Twitter