0

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?

3
  • Found it eventually, but not thrue Google :-)
    – boogjan
    Aug 29, 2018 at 9:32
  • Found it, but not thrue Google: [code] 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 [/code]
    – boogjan
    Aug 29, 2018 at 9:35
  • 1
    Hi boogjan, welcome to the site. I'm glad you found the answer to your question but on this site you are encouraged to type it in the answer box instead of in a comment. You can even accept your own answer by clicking the green check mark next to it.
    – Tom V
    Aug 29, 2018 at 10:17

1 Answer 1

1

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>';
2
  • 1
    In case the original server's MSDB is not available, one could use RESTORE HEADERONLY to check backup's headers.
    – vonPryz
    Aug 29, 2018 at 11:56
  • @vonPryz This is a community wiki answer. Feel free to add any information you find useful into the answer itself.
    – John K. N.
    Aug 29, 2018 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.