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_id) AS backup_set_id, destination_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