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:

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

 

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

Advertisements
By Ivan Donev Posted in DBA

2 comments on “How to get restore history in Microsoft SQL Server

  1. There looks to be an error with one of the joins, i have a database with three files and two file groups but it is reporting 6 files restored. It reports all 3 files restored to both file groups.

  2. Maybe this could work, only tested on one server.
    —————————————————————
    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’,
    bf.filegroup_name,
    bf.file_number,
    bf.physical_drive,
    bf.physical_name
    from msdb..restorehistory rh
    inner join msdb..backupfile bf on rh.backup_set_id = bf.backup_set_id
    order by rh.restore_date desc

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s