SQL Server - Check latest restores

We've written a few posts about backups and how to check which ones have been made. But you can also easily see what restores have been done on an instance. The below script I use to see which databases restores have been done on and when they have been done and what type of restore. The different types are full database (D), differential (I) and transaction log (L) reads. It also shows who did it and information about the backup used; original database, instance, when the backup was done and what backup file was used.

I usually use it when someone wonders what has been restored, but also when checking log shipping.

SELECT TOP 10 h.[restore_date],

h.[destination_database_name],

h.[user_name] AS restored_by,

h.[restore_type],

b.[backup_start_date] AS source_backup_date,

b.[database_name] AS source_database,

b.[server_name] AS source_instance,

b.[user_name] AS backed_by,

f.[physical_device_name] AS source_backup_file

FROM [msdb].[dbo].[restorehistory] h

INNER JOIN [msdb].[dbo].[backupset] b ON h.[backup_set_id] = b.[backup_set_id]

INNER JOIN [msdb].[dbo].[backupmediafamily] f ON b.[media_set_id] = f.[media_set_id]

ORDER BY h.[restore_date] DESC

Hope it comes in handy.

/Björn