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