SQL with Manoj

 
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

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