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
|