Click here to monitor SSC
  • Av rating:
  • Total votes: 26
  • Total comments: 4
Grant Fritchey

Backup Verification: Tips for Database Backup Testing

06 December 2011

A Backup system is merely part of a recovery system. If your backups can't be used to recover the database, then they're useless. Do you regularly make sure that you can restore a database from your backups?

I know that you’re probably running regular database backups. Most people do. Some don’t, but that’s a different discussion. There’s an old saying “Your data is only as good as your last backup.” That’s very true. But, there’s a little known corollary to this: “Your backups are only as good as your last restore.” It’s great that you’re backing up your databases, but you need to do more. You need to test your backups.

The ultimate test for any backup is a restore to a server, but there are a few other things you can do as well. Let’s go over them in the order of their importance in validating that the backups you have are good.

CHECKSUM

The first thing you can do to ensure you have a good backup is to include CHECKSUM in the WITH clause of your backups, like this:

BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'c:\bu\MovieManagement.bak'
WITH INIT, CHECKSUM;

The CHECKSUM is a mathematical construct of a fixed length that gets generated from the page of data. The math will always arrive at the same number for the same page of data. The CHECKSUM value gets written with the page during the backup process and it validates that the page is intact prior to writing it to the backup. This is a good way to validate the media as you do the backup. Then, you can use the CHECKSUMs later during a RESTORE VERIFYONLY operation (see below) to validate the backup media is intact.

This does come with additional cost. It adds overhead, so if you have a very large backup that currently takes a long time, it’ll take longer. But, if you have a very large backup that takes a long time, wouldn’t you want to know that the pages being written to disk are actually intact?

VERIFYONLY

Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this:

RESTORE VERIFYONLY
FROM DISK = 'c:\bu\MovieManagement.bak';

VERIFYONLY will process the backup and perform several checks. First, it can find and read the backup file. Believe it or not, that’s a good first step. Far too many people will assume that they can restore a file that is either incomplete or inaccessible. It also walks through the CHECKSUM information if you used CHECKSUM in the backup (see above). This will validate that the backup media is in place. That can be a costly operation if the backup file is very large, so I don’t know that I’d run this check from my production system if I could help it. Finally, VERIFYONLY checks some of the header information in the backup. It doesn’t check all the header information, so it’s still possible for a backup to pass VERIFYONLY but still not restore successfully to the server. Which brings up the best way to validate your backups, RESTORE.

RESTORE

Like I said at the beginning of this, the best way to know that your backup is intact is to run a RESTORE. It’s very much like taking off and nuking the site from orbit, it’s the only way to be sure. If you successfully run a RESTORE of a backup, then you know that backup is intact. Yes, other things might happen to the file later, but for a moment in time, you’ve validated that your backup and storage mechanisms are working. By the way, notice that all these checks are only concerned with the backup structure and the database structure. What if what you’re backing up is junk?

CHECKDB

You might think that running CHECKDB should be done first, prior to running a backup. And, in most circumstances, you’d be right. However, it is possible to find yourself in a situation where DBCC CHECKDB is extremely expensive. You might not have a big enough maintenance window to run the CHECKDB and get a backup completed. In that case, run the backup, it’s the more important operation. But, you can still validate your database. Because the backup is an exact copy of the database, if there are corruption issues (not found by the CHECKSUM) they’ll be backed up as well. Running a RESTORE and then running CHECKDB will enable you to ensure that your backup is intact and that the data in the backup is intact as well.

Summary

You know that you need to protect the information for your organization. You’re already running your backups. Now take the next step and ensure that those backups are good. Test them. Just remember, that the very best test is a complete restore.

Easier verification with SQL Backup Pro
Want quick, easy backup verification? Make checking for corruption an easy step in your normal backup and restore routines with SQL Backup Pro's automated integrity checks. Find out more.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 26 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: CHECKSUM and backup compression
Posted by: Skreeby (view profile)
Posted on: Wednesday, January 11, 2012 at 10:09 AM
Message: Thanks for the post. I was looking into adding WITH CHECKSUM to our backup jobs and noticed that WITH CHECKSUM is on by default when using backup compression.

Subject: Compression
Posted by: Grant Fritchey (view profile)
Posted on: Tuesday, January 17, 2012 at 5:10 AM
Message: Yes, if you're using SQL Server compression, it turns it on for you.

Subject: GUI vs coding
Posted by: stevegs (view profile)
Posted on: Thursday, January 26, 2012 at 8:48 AM
Message: Something not touched on in your article is what the 'automatic' process of clicking 'verify database' in a maintenance plan task does - does it create the checksum or 'merely' perform a verify only after backup? Or something else partially or completely different? (Yes, I could look it up, but for those who found your article in a panic g**gle search)

Subject: Re: GUI vs coding
Posted by: Grant Fritchey (view profile)
Posted on: Thursday, January 26, 2012 at 12:16 PM
Message: Yes, it does just run a RESTORE VERIFY_ONLY process. Excellent point. I should have covered that. Thank you for bringing it up.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.