Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - Get Latest DB Restore Details

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:  ChillyDBA
Date:      23 Sep 2010
              22 Jan 2013 - Augmented returned columns with the date that the restore file was created by a backup.

Description

Retrieves the last restore details for all databases on a server.  Includes the file name of the backup file and the original backup dates for the file. Also included is the date that the restore file being used was created by a backup command.  Useful if the file naming convention does not include any indication of the content/creation date of the backup .

Also included a variation on the query that returns only the latest restore for each DB.

Mostly useful on non-production servers where restoring copied of production databases is common.

Code

 Get all restores for all DBs:

SELECT 
   
rh.backup_set_id,
   
rh.destination_database_name,
   
rh.restore_date,
   
bs.media_set_id,
   
bmf.physical_device_name                AS source_backup_file_name,
   
bs.backup_start_date                    AS source_backup_start_date,
   
bs.backup_finish_date                   AS source_backup_finish_date,    
   
bs.server_name                          AS source_backup_server_name
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs
   ON rh.backup_set_id bs.backup_set_id
INNER JOIN msdb..backupmediafamily bmf
   ON bs.media_set_id bmf.media_set_id
ORDER BY rh.restore_date DESC



Get latest restores for all DBs:

;WITH LatestRestore AS
(
   
SELECT MAX(backup_set_idAS backup_set_iddestination_database_name 
   FROM msdb..restorehistory 
   GROUP BY  Destination_Database_Name
)
SELECT 
   
lr.backup_set_id,
   
lr.destination_database_name,
   
rh.restore_date,
   
bs.media_set_id,
   
bmf.physical_device_name                AS source_backup_file_name,
   
bs.backup_start_date                    AS source_backup_start_date,
   
bs.backup_finish_date                   AS source_backup_finish_date,    
   
bs.server_name                          AS source_backup_server_name
FROM LatestRestore lr
INNER JOIN msdb..restorehistory rh
   ON lr.backup_set_id rh.backup_set_id 
INNER JOIN msdb..backupset bs
   ON lr.backup_set_id bs.backup_set_id
INNER JOIN msdb..backupmediafamily bmf
   ON bs.media_set_id bmf.media_set_id
ORDER BY lr.destination_database_name


ċ
Get latest restore details for all DBs on a server .sql
(2k)
Andy Hughes,
Jan 22, 2013, 9:56 AM
Comments