SQLINFO – We play with data and technology

Today I am going to give a small script which helps you to find the database restoration details.

SELECT RH.restore_date              AS [RestorationDate], 
       RH.[user_name]               AS [RestoredUser], 
       CASE 
         WHEN rh.restore_type = 'D' THEN 'Database' 
         WHEN rh.restore_type = 'F' THEN 'File' 
         WHEN rh.restore_type = 'G' THEN 'Filegroup' 
         WHEN rh.restore_type = 'I' THEN 'Differential' 
         WHEN rh.restore_type = 'L' THEN 'Log' 
         WHEN rh.restore_type = 'V' THEN 'Verifyonly' 
         WHEN rh.restore_type = 'R' THEN 'Revert' 
         ELSE rh.restore_type 
       END                          AS [RestoreType], 
       RH.destination_database_name AS [RestoredDatabase], 
       RF.destination_phys_name     AS [PhysicalDestiation] 
FROM   msdb.dbo.restorehistory RH 
       INNER JOIN msdb.dbo.restorefile RF 
               ON RH.restore_history_id = RF.restore_history_id

Result: