Thursday, 21 November 2013

Script to find the Restored Database DATETIME



Below script is used to find the restored databases details including specific restored DATETIME.

Script

WITH  CTE_RESTORE_DATETIME
 AS
 (
SELECT  
                                     SD.name AS DatabaseName
                                    , RH.restore_date AS BackUpRestoredDatetime
                                    , ISNULL(RH.user_name, 'Restore Not Happened') AS RestoredBy
                                    , BS.name AS BackUpName
                                    , BS.user_name AS BackupCreatedBy
                                    , BS.backup_finish_date AS backupCompletedDatetime
                                    , BS.database_name AS BackupSourceDB
                                    , BS.server_name AS BackupSourceSQLInstance
                                    , ROW_NUMBER() OVER ( PARTITION BY SD.name ORDER BY RH.restore_date DESC ) AS RestoreOrder
FROM    
                                    SYS.DATABASES AS SD
                                    LEFT JOIN MSDB.DBO.RESTOREHISTORY AS RH ON SD.name = RH.destination_database_name
                                    LEFT JOIN MSDB.DBO.BACKUPSET AS BS ON RH.backup_set_id = BS.backup_set_id
  )
SELECT 
                                      CRD.DatabaseName
                                    , CRD.BackUpRestoredDatetime
                                    , CRD.RestoredBy
                                    , CRD.BackUpName
                                    , CRD.BackupCreatedBy
                                    , CRD.backupCompletedDatetime
                                    , CRD.BackupSourceDB
                                    , CRD.BackupSourceSQLInstance
                                    , CRD.RestoreOrder
FROM   
                                    CTE_RESTORE_DATETIME AS CRD
WHERE  
                                    RestoreOrder = 1
ORDER BY

                                    CRD.DatabaseName