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

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


 
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
GO

SELECT 
	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

db-backup-restore-details

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


Advertisements
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 )

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

%d bloggers like this: