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
SQLskills.com.
“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