Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Do you know if your SQL Server database backups are successful


By:   |   Read Comments (3)   |   Related Tips: More > 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:

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.


Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, January 21, 2015 - 9:44:24 AM - Yaniv Etrogi Back To Top

 

This article speaks about an existing situation that I have experinced several times in the past leading me to develop a tool called RestoreChecker that comes in just to address the points mentioned in the article. We backup daily but never realy know for sure that our backups can be restored when the day comes... and it comes.

Please tale a look at RestoreChecker: http://sqlserverutilities.com/products/restorechecker/

-Yaniv

 


Wednesday, September 18, 2013 - 3:31:22 AM - Angelika Back To Top

good script. in case sensitive context you must use "type" instead of "Type"


Monday, August 27, 2012 - 1:33:50 AM - umasankar Back To Top

 

Good script.Useful to me in time.


Learn more about SQL Server tools