Database Restoration history of all databases

I had to find how frequently and which databases are restored by whom on one of my Development Servers. Following SQL Query does it!

WITH CTE_Restore AS
(SELECT
    db.name ,
    db.create_date,
    db.compatibility_level,
    db.collation_name,
    rst.*,
    Restore_Sequence = ROW_NUMBER() OVER (PARTITION BY db.Name ORDER BY rst.restore_date DESC)
FROM master.sys.databases db
LEFT OUTER JOIN msdb.dbo.restorehistory rst ON rst.destination_database_name = db.Name)
SELECT name as DatabaseName, restore_date, create_date, Restore_Sequence,
user_name as RestoredBy, 
CASE WHEN restore_type = 'D' THEN 'Database'
  WHEN restore_type = 'F' THEN 'File'
  WHEN restore_type = 'G' THEN 'Filegroup'
  WHEN restore_type = 'I' THEN 'Differential'
  WHEN restore_type = 'L' THEN 'Log'
  WHEN restore_type = 'V' THEN 'Verifyonly'
  WHEN restore_type = 'R' THEN 'Revert'
  ELSE restore_type 
 END AS [Restore Type]
FROM CTE_Restore
Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , . Bookmark the permalink.

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