Home > DBA Stuff > SQL DBA – When was the Database restored (Query)?

SQL DBA – When was the Database restored (Query)?

December 13, 2012 Leave a comment Go to comments

When you are working on your DEV database system, very often you restore databases from PRODUCTION systems in order to get fresh and latest data.

Sometimes questions crops in our mind like:

1. When exactly the backups were restored on DEV system.

2. Which user restored the backup on DEV.

3. When the backups were taken on PROD system.

4. What was the backup process start time and end time, so that you are sure you have correct snapshot of data.

5. What was the corresponding database name on PROD, as on DEV you can restore with a different name.

6. What was the backup file location while restoring the databases.

All these questions can be answered by the below sample query:

USE msdb

	rs.destination_database_name AS TargetDBName, 
	rs.restore_date				 AS RestoreDate, 
	rs.user_name				 AS RestoreBy,
	bs.backup_start_date		 AS	BackupStartDate, 
	bs.backup_finish_date		 AS BackupFinishDate, 
	bs.database_name			 AS SourceDBName, 
	bmf.physical_device_name	 AS SourceDBFiles
FROM msdb.dbo.restorehistory rs
INNER JOIN msdb.dbo.backupset bs
	ON rs.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily bmf 
	ON bs.media_set_id = bmf.media_set_id 
ORDER BY rs.restore_date DESC


You can use the above query and tweak as per your needs.

Categories: DBA Stuff
  1. No comments yet.
  1. No trackbacks yet.

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 )

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: