One question that seems to come up on the forums again and again is, ‘how can I see when my database was last backed up?’. There are always a bunch of answers and some very convoluted scripts thrown around.
As a bit of a lunchtime quickie today I thought I’d share with you a script to do just that. For every database on your instance, it’ll tell you the date that the last full, diff and log backup was taken.
We’re pulling the latest dates from msdb.backupset and pivoting the data so that the dates of the different backup types for a given database are all shown on a singe row.
I hope you find it useful…
SELECT name, [FULL], [DIFF], [LOG] FROM (SELECT databases.name, backup_start_date, CASE type WHEN 'D' THEN 'FULL' WHEN 'I' THEN 'DIFF' WHEN 'L' THEN 'LOG' END AS type FROM msdb..backupset backupset RIGHT OUTER JOIN sys.databases databases ON databases.name = backupset.database_name) rawtab PIVOT (MAX(backup_start_date) FOR type IN ([FULL],[DIFF],[LOG])) pivottab