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