Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Find where the last backup was taken

This is a slight variation to the previous script we wrote. Basically we have extended the earlier script.

SELECT A.database_name  as 'DBName',
      A.backup_finish_date as 'Backup Finished',
      B.physical_device_name as 'Backup Filename'
FROM msdb.dbo.backupset A,
      msdb.dbo.backupmediafamily B,
      (SELECT database_name,
                MAX(backup_finish_date) as 'maxfinishdate'
      FROM  msdb.dbo.backupset
      WHERE Type = 'D'
      GROUP BY database_name) C
WHERE A.media_set_id = B.media_set_id AND
      A.backup_finish_date = C.maxfinishdate AND
      A.type = 'D'
ORDER BY DBName

This will give you the list of all the databases with the last date when and where they were backedup.