solving sql server problems for millions of dbas and developers since 2006

Do you know if your SQL Server database backups are successful

By: Jeremy Kadlec | Read Comments | Print

Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009.

Related Tips: More

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:

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

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

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

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.
If backup and recovery are on the forefront of your mind, check out the 15+ tips on MSSQLTips.com.

Related Tips: More | Become a paid author

Last Update: 5/23/2007

Share:

Free SQL Server Learning

Comments and Feedback:

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

Time-strapped database professionals use SQL Monitor to look after their servers. Try it online.

SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check.

Get SQL Server Tips Straight from Kevin Kline.

Join the over million SQL Server Professionals who get their issues resolved daily.

Valuable SQL Server web casts on Performance Tuning, Development, Administration, SSIS and more...