06 December 2011

Backup Verification: Tips for Database Backup Testing

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:

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:

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.

1404-110x80.gifEasier 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.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 46082 times – thanks for reading.

  • Rate
    [Total: 41    Average: 3.9/5]
  • Share

Grant Fritchey

View all articles by Grant Fritchey

Related articles

Also in Backup

Jodie Beay and the Production Database Drift

You make an example database, like NorthWind or WidgetDev in order to test out your deployment system and the next thing you know you're worrying about constraints, backup and security. Then you add an index to the production system and feel a pang of guilt. What would the Devs say? Somehow databases take on lives of their own, populated by the lost souls of users, Developers and DBAs. Has the Redgate DLM Team's practice Forex database somehow come alive?… Read more

Also in Backup and Recovery

SQL Server 2014 Backup Basics

There is nothing mysterious about SQL Server backups. They are essential, however you use your databases. Grant Fritchey explains the basics of database backups and restores with SQL Server 2014.… Read more

Also in Database

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in SQL

SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.… Read more
  • Skreeby

    CHECKSUM and backup compression
    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.

  • Grant Fritchey

    Compression
    Yes, if you’re using SQL Server compression, it turns it on for you.

  • stevegs

    GUI vs coding
    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)

    • Grant Fritchey

      Re: GUI vs coding
      Yes, it does just run a RESTORE VERIFY_ONLY process. Excellent point. I should have covered that. Thank you for bringing it up.

  • nesliemaratas

    Backup Verification: Tips for Database Backup Testing
    Thanks for this article. I have read one more article on DBCC, WITH CHECKSUM and VERIFY ONLY

    http://data-base-recovery.blogspot.in/2016/01/dbcc-checkdb-with-checksum-restore.html

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening