Querying SQL Backup History

What do you do when you want to check the history of your database backups? Do you look at a drive containing your backups? Do you have software like Netbackup for you to open and review? My preference is to run a query in SSMS to get a quick overview.

With a couple tweaks this versatile script can be helpful in a variety of scenarios. You can check a single database, an entire SQL server, or multiple SQL servers. Here is how I use this type of script that queries the backupset table and backupmediafamily table in msdb. I recommend checking out these two tables because there are some good column options that you may want to include when reviewing backups. If you’re feeling dangerous, just modify the scripts below to be SELECT * and see what you get.

Finding Full Backup Info

Sure, you’re backing up your user databases that are being accessed by clients day to day. Are you backing up your system databases? Don’t forget those! It’ll be a frustrating day at the very least if a server crashes and you are left trying to recreate jobs and alerts found in msdb.

Let’s check and make sure we have a full backup of msdb in the last 7 days. We can run this script to view the last full backup:

SELECT top 1 s.database_name
	,m.physical_device_name
	,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
	,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
	,s.backup_start_date
	,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
	,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
	,CASE s.[type]
		WHEN 'D'
			THEN 'Full'
		WHEN 'I'
			THEN 'Differential'
		WHEN 'L'
			THEN 'Transaction Log'
		END AS BackupType
	,s.server_name
	,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'msdb' and s.type = 'D'
ORDER BY backup_start_date DESC
	,backup_finish_date
GO

This same script is also good to run if using Central Management Server to check multiple servers at a time. Run it against a group and get the latest info for each server. If running against a single server, removing the “top 1” will show more history.

Checking Transaction Log Backups

For another example, let’s pick one database and make sure our hourly transaction log backups are occurring as expected. Testing with an ExampleDB database, we’ll modify our script to be:

SELECT s.database_name
	,m.physical_device_name
	,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize
	,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
	,s.backup_start_date
	,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
	,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
	,CASE s.[type]
		WHEN 'D'
			THEN 'Full'
		WHEN 'I'
			THEN 'Differential'
		WHEN 'L'
			THEN 'Transaction Log'
		END AS BackupType
	,s.server_name
	,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = 'ExampleDB' and s.type = 'L'
ORDER BY backup_start_date DESC
	,backup_finish_date
GO

The script will give us results looking like this (minus the blacked out data):

Good Enough?

It’s nice to check backup history. I hate to break it to you but just because you have backups doesn’t mean they are any good. Make sure you’re taking some time to verify your backups by restoring them!

Thanks for reading!