In a previous entry, I posted T-SQL code that will list the last restore dates for all db's on a SQL Server. The original purpose was to verify the dates that production backups were restored to the test environment.
This enhanced script will also display the name of the MDF file and the name & location of the backup file that was used for the restore. This can serve as a double-check that the dev servers are running the latest copies from production.
CREATE PROC [dbo].[procLastRestoreDetails]
@database_name AS varchar(50) = NULL
RH.restore_date AS Last_Restored_DateTime,
RF.destination_phys_name AS Restored_To_Database_Location,
BMF.physical_device_name AS Restored_From_Backup_File
INNER JOIN msdb.dbo.restorefile RF
ON RH.restore_history_id = RF.restore_history_id
INNER JOIN msdb.dbo.backupset BS
ON RH.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily BMF
ON BS.media_set_id = BMF.media_set_id
RF.destination_phys_name LIKE '%.mdf'
AND RH.backup_set_id =
destination_database_name = BS.database_name
BS.database_name = @database_name
@database_name IS NULL