Automated DB Restore Based on SQL Backup FileName

• 
  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"'
  

  petey January 26, 2011 19:52

  - 0 +
• 
  Thank you sir, thats perfect.

  pcorujo January 27, 2011 10:52

  - 0 +

Add comment

Please sign in to leave a comment.