March Madness – SQL Server System Tables – backupset

Ever feel the need to dig through the details regarding your database backups? Well today is your lucky day! I will show you a few details regarding the msdb..backupset table.

What is this table for?

This table will contain one row for each successful backup set, which is the same as saying it contains one row for each successful database backup. Go ahead and see for yourself. Run this code:

SELECT COUNT(*)
FROM msdb..backupset
GO

BACKUP DATABASE [AdventureWorks] TOÂ 
DISK = N'C:\AdventureWorks_1.bak', 
DISK = N'C:\AdventureWorks_2.bak'
WITH INIT
GO

SELECT COUNT(*)
FROM msdb..backupset
GO

You should see that you even if you stripe a database backup across two files that it is only counted as one successful operation and the count of the number of rows in the msdb..backupset table has incremented by one.

Why should you care?

Because every DBA cares about their backups, right? So you want to know that your backups are taking place. I would run a script similar to the following (good for SQL 2005 and above):

SELECT d.name as [DBname]
, [hoursSinceFull] = max(isnull(datediff(hour,b.backup_start_date,getdate()),-1))Â 
, [hoursSinceDiff] = max(isnull(datediff(hour,bi.backup_start_date,getdate()),-1))
FROM [master].[sys].[databases] d with (nolock)
LEFT JOIN [msdb]..[backupset] b  with (nolock) on d.name = b.database_name
and b.backup_start_date = (select max(backup_start_date)
from [msdb]..[backupset] b2
where b.database_name = b2.database_name and b2.type = 'D')
LEFT JOIN [msdb]..[backupset] bi  with (nolock) on d.name = bi.database_name
and bi.backup_start_date = (select max(backup_start_date)
from [msdb]..[backupset] b3
where bi.database_name = b3.database_name and b3.type = 'I')
where d.name <> 'tempdb'Â 
and d.state = 0Â 
and d.source_database_id is null  
group by d.name

The above code returns the name of the database, the number of hours since the last full backup, and the number of hours since the last differential. Since we were a shop that did weekly full backups and nightly differentials I would check to make certain that we had a full dump less than 169 hours old, and a differential less than 25 hours old. If either one of those numbers where not true then I would have Operations Manager alert me. (I leave it as an exercise for the reader to see why I chose 169 hours when there are only 168 hours in a week, and for 25 hours when there are only 24 hours in a day.)

Where else is this information?

The msdb database has several tables related to database backups including the backupmediaset and backupfile. Go ahead and poke around for more info.

4 thoughts on “March Madness – SQL Server System Tables – backupset”

  1. Well, if you observe Daylight Savings Time, one day a year has 25 hours in it 🙂

    This is really good info. I didn’t know this was there at all. Haven’t done much with MSDB since the DTS days.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.