SELECT A.restore_history_id
, A.restore_date –date database was restored
, A.destination_database_name
, A.user_name
, A.restore_type
, B.backup_finish_date –date of backup used to restore database
, C.physical_device_name –backup file used to restore database
, A.stop_at
, A.stop_at_mark_name
, A.stop_before
FROM msdb.dbo.restorehistory A
LEFT JOIN msdb.[dbo].[backupset] B
ON A.backup_set_id = B.backup_set_id
LEFT JOIN msdb.[dbo].[backupmediafamily] C
ON B.[media_set_id] = C.[media_set_id]
WHERE A.restore_date
= ( SELECT MAX(A1.restore_date)
FROM msdb.dbo.restorehistory A1
WHERE A1.destination_database_name = A.destination_database_name )