Wednesday, December 12, 2012

Oh My God! SQL Server Backup is Corrupt too

It is a dark secret that once in a while every database administrator face SQL server backup corruption issue. Some of them talk on this topic publicly & some does not but it is true. I have seen in forums & newsletters where; people talk freely not on SQL data file corruption only but SQL backup file corruption too.

Here is a statement given by Paul Randlal, who is the CEO of

You don't have a backup until you have done a restore

Meaning: The meaning of statement is that if you have SQL server database backup that is corrupt due to any reason, you can’t restore it over original database but you can check the validity of the backup that is going to help you in disaster recovery.

Check integrity of database before backup: It is a very simple & important talk of SQL server DBA. You can do this by running DBCC consistency checks before taking a full backup of your database otherwise you may be backing up a corrupt database.

Check the Integrity of Backup itself: You can do this by using 'WITH CHECKSUM' option along with backup command. Backup command with Checksum option checks the checksum for whole backup steam and save it into the backup. It ensures you that your database backup is not corrupt.

So there are a lot of things that you can do to minimize the change of backup corruption. Now the main point comes in picture, if you don't have followed any of the above suggestions before making a backup of your database and backup is corrupt.

How to Restore Database? Many people are got frustrated or tensed when they face this situation but I will suggest you keep your mind cool. You can do one thing, allow restore to ignore error. There is an option in restore that is known as continue-after-error. It enables you to restore the backup even if corruption is found in the backup stream. After this; your database may go inconsistent state. You can use DBCCCheckDB command to repair SQL database. DBCC CheckDB offers three repair options: repair_fast, repair_rebuild, and repair_allow_data_loss. However, if you are still not able to restore your database then it is the best time to use any 3rd party backup repair program.

Summary: Make sure you have validated the database before backup & backup after you have finished with backup. Also, try to make more than one backup if first backup got corrupted then you will have another option for restore & recovery. 

No comments:

Post a Comment