T-sql – TSQL retrieve backup date from database after restore

backupcloudrestoret-sql

Usecase: MSSQL Database is hosted in the cloud, I download the database thrue FTP and restore it on-premise automatically for reporting.

Now I want to retrieve the date/time when the backup was made, from the restored database, to show my users how old the data is that they are working with.

In my local MSDB, I only see when the database was restored, not when it was originally back-upped in the cloud. I know the date-time is in the .bak, but that is hard to query.

Any suggestions?

Best Answer

Answer from author's comment converted to community wiki

select  top 1 
        s.backup_finish_date 
from    msdb.dbo.backupfile f 
left outer join 
        msdb.dbo.backupset s 
    on  s.backup_set_id = f.backup_set_id 
where   f.logical_name  = '<name of mdf-file>' 
order by 
        f.backup_set_id desc

In case the original MSDB is not available, or backup history has been purged, the data is available within backup headers. Use RESTORE HEADERONLY to retrieve the header contents. In the header, there's about 60 pieces of metadata; refer to the Microsoft documents for detailed description.

For example,

restore headeronly from disk = N'<path-to-backup-file.bak>';
Related Question