The problem with using
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.
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014.bak' ;
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
MSDB only refer to the local server, not the backup metadata information.
Query posted from Marc_c for preservation:
;WITH LastRestores AS
DatabaseName = [d].[name] ,
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
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.