Last Restore Details for SQL

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

SELECT
BS.database_name,
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
FROM
msdb.dbo.restorehistory RH
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
WHERE
RF.destination_phys_name LIKE '%.mdf'
AND RH.backup_set_id =
(
SELECT
MAX(backup_set_id)
FROM
msdb.dbo.restorehistory
WHERE
destination_database_name = BS.database_name
)
AND
(
BS.database_name = @database_name
OR
@database_name IS NULL
)
ORDER BY
BS.database_name