UNDERCOVER TOOLBOX: When was my last backup taken?

calendarOne 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: