DBCC CHECKDB Repair Options – Which is the best

Begin Learning Cyber Security for FREE Now!

Today, the IT-requirement is experiencing a significant overhaul in its approach. Most SQL Administrators are shifting from standard SQL management to intelligent SQL management, as present circumstances demand to act fast, proactive and strategic. Running DBCC CHECKDB command is one such proactive measure taken by SQL DB Administrators to check for any inconsistency and building a maintaining the backup of the database with a regular backup-health-check is a strategic approach to combat corruption issues.

This article highlights the steps to take to repair the corrupt database, but don’t overlook to investigate the cause of corruption. Thankfully, the proactive approach of running DBCC CHECKDB command ensures thorough check of the disks and SAN, but keeping an eye on Windows Event Logs is equally essential as it may also display an error message to help in investigations.

Investigate the cause

Run DBCC CHECKDB command and analyze the output

The investigations reveal the following points:

Corruption on page 1: 94299

Index ID =0, the database corruption is in “clustered index”, and the data should be repaired using an appropriate “Repair method”

An error message was displayed on running the DB-Check command. Let us investigate the pages which are affected, the type of data which is modified, index entries of changed data, and much more

The corruption was found in Table as well as database

Corruption is in clustered index

Database. 1 and row no. 94299 shows corruption

Repair possible by running the command – Repair_Allow_Data_Loss

As long as the database is available in the repairable form, the last line of the output of DBCC CHECKDB command always indicates the repair option to be used. The most prevalent repair options include: REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

When considering a repair solution for your corrupt SQL database, look for a solution that addresses the three principal challenges of database repair –

Repairing of corrupt database

No data loss during repair process

Database accessibility after repair

Let’s take an in-depth look at the methods needed for database repair, and what if these pose limitations?

REPAIR_ALLOW_DATA_LOSS

Command to repair the corruption in complete database, with the following formula: Delete broken data and fix all Structural Linkages

REPAIR_FAST

Retains syntax for backward compatibility and, does not perform any repair actions.

REPAIR_REBUILD

Repairs the database where data loss is not possible. The quick repair mechanisms include repair the missing rows in non-clustered index, and the time-consuming mechanism includes rebuilding an index.

Limitations of REPAIR-Commands

The limitation of this command are:

In REPAIR_ALLOW_DATA_LOSS, the Database strings which are attached to the SQL Database are repaired and the strings which are not-attached are deleted. This may lead to loss of data in the repaired MDF file. By allowing the Repair_Allow_Data_Loss feature, you are also allowing DBCC CHECKDB repair option to delete a part of SQL database to accomplish the repair.

The REPAIR-FAST option does not perform repair functions

REPAIR_REBUILD option is unable to perform the repair in case of clustered index.

Store SQL Database backup

When a corruption issue is detected by DBCC_CHECKDB command, the best course of action is to restore the database from last full backup.

Analyze the state of backup before conducting the Restore process, and take care to perform backup considering the following pre-conditions:

Backup to be performed with automatic full backup

Understand the level of damages if a database backup is not complete. Restore from database backup is helpful only when the last backup is recent and up-to-date

SQL repair options are never simple, but these are essential to retrieve data from the corrupt mode and let the database be up and running within no time.

Power the intelligent SQL Database Repair Software

MS SQL Database should not be in inaccessible state at any point in time, hence; it is advisable to remove the corruption in database and take help of SQL Repair software. One of the most performing and result-oriented software is Stellar Phoenix SQL Database Repair Software with a proven track record of repairing the corrupt database without any data loss.

As the organizations are accumulating huge databases and accentuating its accessibility to more and more users, it has become increasingly evident that there is a need for more endpoints to manage, and more data to monitor. In that case, regular backup of database plays an essential part. If the database management results in some error and its backup is not up-to-the-mark, then there is only one option i.e., to repair the database with Stellar Phoenix SQL Database Repair Software.

If you are ever in a position where “REPAIR_ALLOW_DATA_LOSS” option is required, you should review your maintenance and backup policies. This can and will not hesitate to destroy the logical integrity of a database!

Yeah, backup is the best option to handle database corruption. In my career, I have seen so many cases where the backup was not updated or corrupted.In that case, the user took the help of a reliable third party SQL repair like Stellar Phoenix SQL Database Repair.

Our Revolution

We believe Cyber Security training should be free, for everyone, FOREVER. Everyone, everywhere, deserves the OPPORTUNITY to learn, begin and grow a career in this fascinating field. Therefore, Cybrary is a free community where people, companies and training come together to give everyone the ability to collaborate in an open source way that is revolutionizing the cyber security educational experience.