“How old is this data?”

“When was this database restored?”

“How old is this backup?”

These are common question in development environments.  A fast little t-sql query can tell you the following:

1. The date and time of the last restore.
2. The date and time the backup which was restored was originally taken.
3. The user who took the backup.
4. The user who restored the backup.
5. The name of the backup.
6. The machine or SQL instance the backup was taken from.

Use this information to impress your DBAs (and to impress upon your team that it’s time to refresh your data!)

WITH    restore_date_cte
          AS ( SELECT   d.name AS DatabaseName
                      , rh.restore_date AS BackUpRestoredDatetime
                      , ISNULL(rh.user_name, 'No Restore') 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 --get the most recent
                        ( PARTITION BY d.name ORDER BY rh.restore_date DESC ) AS RestoreOrder
               FROM     sys.databases AS d
                        LEFT JOIN msdb.dbo.restorehistory AS rh
                            ON d.name = rh.destination_database_name
                        LEFT JOIN msdb.dbo.BackupSet AS bs
                            ON rh.backup_set_id = bs.backup_set_id
             )
    SELECT  rdc.DatabaseName
          , rdc.BackUpRestoredDatetime
          , rdc.RestoredBy
          , rdc.BackUpName
          , rdc.BackupCreatedBy
          , rdc.backupCompletedDatetime
          , rdc.BackupSourceDB
          , rdc.BackupSourceSQLInstance
          , rdc.RestoreOrder
    FROM    restore_date_cte AS rdc
    WHERE   RestoreOrder = 1
    ORDER BY rdc.DatabaseName

Note: Removing the “WHERE RestoreOrder = 1” will give you the entire restore history from the restoreHistory table.

Another note: There was an older version of this script in this post previously. I believe the script above is much easier to understand and modify than the old version. If you prefer more complicated code the old version is included below for posterity’s sake.

USE MSDB
GO

SELECT TOP 1 WITH TIES
        d.name
      , rh.restore_date AS BackUpRestoredDatetime
      , ISNULL(rh.user_name, 'No Restore') 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
FROM    sys.databases AS d
        LEFT JOIN dbo.restorehistory AS rh
            ON d.name = rh.destination_database_name
        LEFT JOIN BackupSet AS bs
            ON rh.backup_set_id = bs.backup_set_id
ORDER BY RANK() OVER --get the most recent
        ( PARTITION BY d.name ORDER BY rh.restore_date DESC )