Do you know if your SQL Server database backups are successful

By: Jeremy Kadlec   |   Comments (4)   |   Related: > Backup

Problem

Are you absolutely sure?  Are you?  After hearing a presentation from Microsoft's Paul Randall, at a recent Northern Virginia SQL Server Users Group session, I wondered how many DBAs\Developers\Network Admins realize their SQL Server database backups are failing.  According to Paul they find out when it is too late - when they are experiencing corruption and facing lost data after significant amounts of downtime.  Both of which are detrimental for the success of the organization and inexcusable for DBAs\Developers\Network Admins responsible for SQL Server Service Level Agreements (SLAs). 

Solution

Good people make mistakes.  But not validating that backups are successful on a regular basis is a break down in a critical process.  Most likely when a backup fails an email is sent or an entry is written to a log which notifies the DBAs\Developers\Network Admins responsible for the SQL Server backups.  What happens if a configuration parameter changes and you have not received many emails for a problem that has been festering for days, weeks or months?  Depending on the application and process used to perform the SQL Server database backups, another viable check is related to the failed SQL Server Jobs, but these too must be checked to ensure they are operating successfully.

One way to approach this issue is to setup a group of SQL Server Agent Jobs to monitor the overall backup process from the last 24 hours.  This means that on a daily basis a notification will be sent for the backups from the previous day.  When you do not receive the notification, you immediately know that a problem has occurred and the overall process needs to be researched.  Your backup schedule, environment (servers, dependencies, etc.), and notification tools dictate exactly how this process should be approached.  The common thread is the T-SQL code that needs to be executed to capture full, transaction log, differential or file\file group backups.  The code below can be plugged into the notification tool of your choice to determine the backup status for the types of backups that you issue on a regular basis:

SQL Server Full Database Backups

SELECT database_name, backup_start_date, type, * 
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

SQL Server Transaction Log Backups

SELECT database_name, backup_start_date, type, * 
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO

SQL Server Differential Backups

SELECT database_name, backup_start_date, type, * 
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO

SQL Server File\File Group Backups

SELECT database_name, backup_start_date, type, * 
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO

Next Steps

• If you do not have a sophisticated high availability or disaster recovery tool, then you are relying on your SQL Server database backups as your first and last line of defense when an issue occurs.  As such, make sure you put the time into your backups so when you need them they will do the job. 
• Considering how important backups are, this is one thing I keep in mind: "backups are for show, but the restores are for job security!"  What this means is that backups are important from the beginning; but not being able to successfully restore the databases, yields those same backups as worthless.  So be sure to test your backup\recovery process and the corresponding application as you change your process and implement the latest versions of your backup application.
• From a testing perspective, check out the SQL Server DBA Checklist tip which has some testing recommendations.

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips