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.
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.
can we append any field of this database like changing the expiry_date
Not sure what you are asking, but if you want to modify the code for your needs, please go ahead.