In this article we explore why it is important to maintain backup integrity.
Although backing up of data is indispensable, there are occasions, when a backup might prove to be more harmful than helpful. While backing up data, you might not realize, but it might get corrupted. And you will come to know about it only when you attempt to use the backups for restoration of data. In such a situation, a backup file proves to be more of a problem than a solution. A corrupted backup might fail entirely due to no integrity, or may have lots of problems in loading. There are many reasons as to why does a backup file get corrupted, the most common reason is, it is stored in a corrupted system.
If you store your backups on the same system as your online database, there are high chances of your backups getting corrupted. If there is any kind of virus in the database, it can be transferred to the backup file as well, thus corrupting the backup file. This is why it is always recommended to store backups on a different system, to always keep them safe from any kind of threats. Restoring backups on another system or server not only keeps them protected but also makes it easy to check the integrity of the system. The 2005 edition of SQL Server came up with features that allowed for checking the integrity of the backups, without having to restore them.
Features Available in SQL Server to Maintain Backup Integrity
SQL Server editions starting from 2005 onwards comes with a feature ‘WITH CHECKSUM’, this is to be used while taking a backup. It is suitable for all kinds of backups. If the backup used is Full or Differential, (with the later usually being the case) with checksums enabled in the database, then this process will also lead to the testing of all existing page checksums, when the pages are read during backup processes. The backup will fail instantly in-case of a bad page checksum.
This feature is of great help, and prevents accidental backing up of corrupted files. You can use particular commands, after completing the backup, to verify it. The verification involves recalculating the checksum value over the complete backup stream and comparing it with the original present in the backup. In-case of full and differential backups, all the pages in the backup will be tested for page checksums. If there is any problem, it only indicates that the backup is corrupted.
Sometimes it might also happen that you have no choice but to backup a corrupted database. This is usually done in situations, where the corrupted database is the only copy left. In such a situation, attempts are made to repair the corrupted database. When you are backing up a corrupted database, the system will not allow it. And will thus give you warnings. To continue backing a corrupted database, which is low on integrity, use the WITH CONTINUE_AFTER_ERROR choice. This will allow you to complete backing up a corrupted database.
Maintaining Data Integrity is Critical for the Success Any Recovery Process
While attempting to recover a corrupted SQL file, one must give credence to the need for maintaining complete data recovery. To achieve this end you should use a sophisticated mdf recovery tool like DataNumen SQL Recovery. This class leading tool offers the best recovery rate for SQL recovery and can bring back all the records in perfect shape. Moreover if you have records that got deleted by mistake, just run this tool to get them back in a jiffy.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com