How to get restore history in Microsoft SQL Server

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

image

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 Smile

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.