UNDERCOVER TOOLBOX: When was my last backup taken?

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