Have you ever wondered how to extract restore history from your SQL Server instance? I believe the answer is yes. Well, so have I! And so far I have been using two different methods for getting what I want.
Number 1: the standard report in SSMS – right click on the database –> Reports –> Standard Reports –> Backup and Restore Events
Number 2: using the below query:
[sourcecode language='sql' padlinenumbers='true'] SELECT rh.destination_database_name as 'Database Name', rh.[user_name] as 'Username', CASE rh.restore_type WHEN NULL THEN 'NULL' WHEN 'D' THEN 'Database' WHEN 'F' THEN 'File' WHEN 'G' THEN 'Filegroup' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log File' WHEN 'V' THEN 'Verifyonly' WHEN 'R' THEN 'Revert' END as 'Restore Type', CASE rh.[replace] WHEN NULL THEN 'NULL' WHEN 1 THEN 'YES' WHEN 0 THEN 'NO' END AS 'Database Replaced', rh.restore_date as 'Date Restored', rfg.[filegroup_name], rf.file_number, rf.destination_phys_drive, rf.destination_phys_name FROM msdb..restorehistory rh inner join msdb..restorefilegroup rfg on rh.restore_history_id = rfg.restore_history_id inner join msdb..restorefile rf on rh.restore_history_id = rf.restore_history_id order by rh.restore_date desc [/sourcecode]
This script is based on the queries in the report from SSMS and utilizes three system tables in msdb – restorehistory, restorefile and restorefilegroup. These three tables are quite convenient in getting the info you want and easily transfer it to Excel or something else and make it “detailed report” ready