Date a backup was taken even if restored to another server

Question

I have a data warehouse platform that is loaded from backups of a set of application databases restored locally onto the server. We have a requirement to know what time the backup was taken - i.e. the as-at time on the source system that the data loaded into the EDW was taken.

This question discusses getting last restore dates.

The table msdb.restorehistory has a column backup_taken_from. Will this still hold the backup date if the backup is restored onto another server - i.e. is this populated from metadata in the backup files?

Shaulinator · Answer 1 · 2018-06-19T15:49:10.037

The problem with using MSDB and Master is that those reference the server they reside on. So if you run those commands, it will refer to restores on the server you are querying and not the restores on the server you are referring to in this circumstance.

The backup files itself should contain the information you need however.

RESTORE HEADERONLY   
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.bak' ;  
GO

The above example is doing a check on a backup file and in there, I can see the BackupStartDate shows '2014-07-17 16:18:18.000' and BackupFinishDate shows '2014-07-17 16:18:22.000'.

When I use the query you had linked in the example referenced by Marc_c on Stack Exchange, I can see the same backup I stated above. The Create_Date is '2018-05-17 13:32:39.700' and the Restore_Date is '2018-05-17 13:32:39.357'. This illustrates my point that the Master and MSDB only refer to the local server, not the backup metadata information.

Query posted from Marc_c for preservation:

;WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

See the MSDN on HEADERONLY.

See the MSDN on Result Sets from HEADERONLY

I would create a procedure that would loop through the files you need and have it capture the output of the RESTORE HEADERONLY and then log that to the table for your reference later to meet your requirements.

Martin Smith on Stack Exchange with an OPEN ROWSET solution to logging the data.

Dan · Answer 2 · 2018-06-19T22:35:37.107

Shaulinator's solution is good, and relatively foolproof. A lower effort alternative is to have the backup timestamp included in the backup file name (which I feel is good practice even outside this particular scenario).

Since you can always retrieve the backup file name that was used for any particular restore, this will allow you to determine what time that backup was taken, even if it was restored to another server. For example:

use msdb

;with restorefiles as 
(
       select rh.destination_database_name
             , rh.restore_type
             , bmf.physical_device_name
             , rh.restore_date
             , row_number() over (partition by rh.destination_database_name order by rh.restore_date desc) as RowNum
       from restorehistory as rh
       join backupset as bs
       on rh.backup_set_id = bs.backup_set_id
       join backupmediafamily as bmf
       on bs.media_set_id = bmf.media_set_id
       --where restore_type = 'D'
)
select * 
from restorefiles 
where rownum = 1

If I run that on one of my log shipping targets (as an example), it retrieves all the backup files including their original timestamps.

You can un-comment the where clause within the CTE if you're interested in only looking at a particular restore type (D = FULL, I = DIFF, L = LOG).

Date a backup was taken even if restored to another server

2 Answers2