Practical SQL Server

SQL Server Database Corruption, Part VIII: The Importance of Backups

In Part VII of this series on SQL Server Database corruption we touched upon how backups can be used as an additional means of early detection for corruption – by making sure to validate the checksums of all data being backed up.

The Importance of Backups In Recovering from Corruption

But, did you know that backups are your BEST line of defense in recovering from MOST forms of database corruption? (If you’ve been following along in previous posts, then you already knew this.)

Granted, there is one form of corruption that I’ve encountered that CAN safely be corrected without the use of backups (or, at least, I’ve seen cases where I was able to correct it without the absolute NEED for backups – even though I had them standing by as a precaution and even though I FIRST tested these corrections on copies of my databases made from backups). But this specific type of corruption really isn’t related to the kinds of IO subsystem corruption that have been the focus of this series of posts – and, instead, this type of ‘corruption’ really deals with a question of meta-data getting a bit out of sync and needing to be updated or refreshed via the use of DBCC UPDATEUSAGE().

Otherwise, for an excellent overview of what your options are for dealing with corruption WHEN you don’t have viable backups available, check out Paul Randal’s excellent Tech Ed Session where he covers various mechanisms of recovery without using backups. (And note that while I’m making the case from Paul’s video that you SHOULD have backups available – given what’s required otherwise (and given how low your potential for success can be without backups) – Paul makes similar arguments but ALSO addresses VERY advanced scenarios where UP-TIME is more important than DATA INTEGRITY).

Hopefully, though, in watching that presentation you’ll come away with a much more healthy respect for how important backups can be, and for how you need to be regularly testing your backups to make sure that they’re viable for use in disaster recovery scenarios.

Backups are an Essential Component of Disaster Recovery Plans

It sounds dumb, but backups are an essential component of any worthwhile disaster recovery plan. And while that definitely includes most concerns around database corruption, it’s important to call this simple fact out as it’s increasingly common to find cases where many organizations confuse High Availability solutions as a replacement for backups as a Disaster Recovery.

Consequently, as we’ll see in the next post in this series, it’s impossible to over-stress the need to not only have regular backups on hand but to regularly test them to both ensure that they’re valid and to ensure that you are comfortable working with and restoring from backups.

Part IX: Responding to Corruption

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.