Sql-server – Date a backup was taken even if restored to another server

backupsql server

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?

Best Answer

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.

Related Question