It’s a very common practice every DBA verifies where restored Database is as per expected or not, and there may be a question where restored database is restored from proper backup or not. Here is a query which will show how we can identify the file(s) that was used for the restore, when the backup actually occurred and when the database was restored.

SELECT RH.[destination_database_name] Destination_DB_Name, RH.[restore_date] Restore_Date, BKS.[backup_start_date] Backup_StartDateTimeBKS.[backup_finish_date] Backup_FinishDateTime,
as [Source_Database_Name], BMF.physical_device_name] as [Backup_file_used_for_Restore],
RF.file_number File_number,RF.destination_phys_drive Restore_TragetDrive,
FROM msdb..restorehistory RH
INNER JOIN msdb..restorefile RF on RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb..backupset BKS ON RH.[backup_set_id] = BKS.[backup_set_id]
INNER JOIN msdb..backupmediafamily BMF ON BKS.[media_set_id] = BMF.[media_set_id]
ORDER BY RH.[restore_date] DESC

Please comment if any suggestion or modification J

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s