Handy Tip: Date when a SQL Database was restored

I recently had to confirm when a database was restored. The information is in the MSDB database, and here is the SQL you can use to get the restore history of all the databases on the server:

USE msdb;

SELECT
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
FROM
RestoreHistory h
INNER JOIN BackupSet b
ON h.backup_set_id = b.backup_set_id
INNER JOIN BackupFile f
ON f.backup_set_id = b.backup_set_id
ORDER BY
RestoreDate

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s