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?

  • Found it eventually, but not thrue Google :-) – boogjan Aug 29 '18 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 '18 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 - try topanswers.xyz Aug 29 '18 at 10:17
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>';
  • 1
    In case the original server's MSDB is not available, one could use RESTORE HEADERONLY to check backup's headers. – vonPryz Aug 29 '18 at 11:56
  • @vonPryz This is a community wiki answer. Feel free to add any information you find useful into the answer itself. – John aka hot2use Aug 29 '18 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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