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
|
