view database restore history in sql server

To view restore history for particular database or all the databases on a single SQL Server you can query restorehistory table in msdb database. This script comes very handy when you want to see when was the last restore was done on your QA or Dev servers.

Here is the T-SQL script, if you want to view restore history of particular database then change the SET @dbname = NULL to SET @dbname = 'Your_Database_Name'

Declare @dbname SYSNAME
SET @dbname = NULL
SELECT
destination_database_name as 'Database Name',
[user_name] as 'Username',
CASE 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 [replace]
WHEN NULL THEN 'NULL'
WHEN 1 THEN 'YES'
WHEN 0 THEN 'NO'
END as 'Database Replaced',
restore_date as 'Date Restored'
FROM msdb..restorehistory
where destination_database_name = CASE
 WHEN @dbname IS NOT NULL THEN @dbname 
ELSE destination_database_name END
order by restore_date desc

The above script will output following fields.
Database Name
Username of the person restored
Type of restore
If existing data database was replaced
Date of restore



 5/5/2008


Add Comments
Name: *
Email: *
URL:
Comments: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.