How to find the Database Restore Details in SQL Server 2008

It is often useful to know the details from where the database is restored.The restore history of the database is stored in the msdb table "restorehistory".I recently came across this query which gives the complete information of restore history including the Restored Login , From & To Location of the Files

SELECT
rsh.destination_database_name AS [Database],
rsh.user_name AS [Restored By],
CASE WHEN rsh.restore_type = 'D' THEN 'Database'
WHEN rsh.restore_type = 'F' THEN 'File'
WHEN rsh.restore_type = 'G' THEN 'Filegroup'
WHEN rsh.restore_type = 'I' THEN 'Differential'
WHEN rsh.restore_type = 'L' THEN 'Log'
WHEN rsh.restore_type = 'V' THEN 'Verifyonly'
WHEN rsh.restore_type = 'R' THEN 'Revert'
ELSE rsh.restore_type
END AS [Restore Type],
rsh.restore_date AS [Restore Started],
bmf.physical_device_name AS [Restored From],
rf.destination_phys_name AS [Restored To]
FROM msdb.dbo.restorehistory rsh
INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id
INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id
INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id


You can apply filter criteria such as the restore date , Database by addding a where clause to the existing query as shown below

WHERE
rsh.restore_date >= DATEADD(dd, "No of Past Days" , GETDATE())
AND destination_database_name = ISNULL( "DB Name", destination_database_name)
ORDER BY rsh.restore_history_id DESC