SQL Server – Review Database Restore Timeline

Background

Reviewing Database Restore timeline and wasted to share what we have thus far.

 

Metadata

SQL

/*

    a) restorehistory (Transact-SQL)
    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/restorehistory-transact-sql

*/
; with cteRestoreHistory
(
      [restoreHistoryID]
    , [databaseName]
    , [restorer]
    , [restoreDate]
    , [restoreStopAt]
    , [restoreType]
    , [backupSetID] 
    , [sequenceNbr]
)
as
(

    select 
              [restoreHistoryID]
                = tblRH.[restore_history_id]

            , [databaseName]
                = tblRH.[destination_database_name]

            , [restorer]
                = tblRH.[user_name]

            , [restoreDate]
                = tblRH.[restore_date]

            , [restoreStopAt]
                = tblRH.[stop_at]

            , [restoreType]
                = tblRH.restore_type

            , [backupSetID] 
                = tblRH.[backup_set_id] 

            , [sequenceNbr]
                = DENSE_RANK() 
                    OVER 
                    (

                        PARTITION BY 
                            tblRH.[destination_database_name]

                        ORDER BY 
                            tblRH.[restore_date] DESC
                    )

    from   msdb.[dbo].[restorehistory] tblRH


)

, cteRestoreType
(
      [restoreType]
    , [restoreTypeLiteral]
)
as
(
    select 
              'D'
            , 'Database'
    union
    select 
              'F'
            , 'File'
    union
    select 
              'G'
            , 'FileGroup'
    union
    select 
              'L'
            , 'Log'

)
select 
          [database] 
            = tblSD.[name]

        , [dbid]
             = tblSD.[database_id]

        , [restoreInitatedBy]
             = tblRH.[restorer]

        , [restoreDate]

        , [restoreType]
             = tblRH.[restoreType]

        , [restoreTypeDescription]
            = cteRT.restoreTypeLiteral

        , [backupMachineName]
            = tblBS.[machine_name]

        , [backupInitiatedBy]
            = tblBS.[user_name]

        , [databaseVersion]
            = tblBS.database_version

        , [backupFile]
            = tblBMF.physical_device_name

        , [originalBackupStartDate]
            = tblBS.backup_start_date

        , [originalBackupCompletionDate]
            = tblBS.backup_finish_date

        , [originalBackupDuration (in minutes)]
            = datediff
                (   
                      minute
                    , tblBS.backup_start_date
                    , tblBS.backup_finish_date
                )

from   sys.databases tblSD

inner join cteRestoreHistory tblRH

        on tblSD.[name] = tblRH.[databaseName]

inner join msdb..backupset tblBS

    ON tblRH.[backupSetID] = [tblBS].[backup_set_id]

INNER JOIN msdb..backupmediafamily tblBMF
    ON [tblBS].[media_set_id] = [tblBMF].[media_set_id] 

left outer join cteRestoreType cteRT
    on tblRH.[restoreType] = cteRT.restoreType


where tblRH.sequenceNbr = 1

order by

          tblSD.[name]
        , tblRH.[restoreDate] desc


Output

Sample

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s