DBCC CHECKDB Repair Options – Which is the best

February 9, 2018 | Views: 3339

Begin Learning Cyber Security for FREE Now!

FREE REGISTRATIONAlready a Member Login Here

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 

DBCC CHECKDB Result

The investigations reveal the following points:

  1.  Corruption on page 1: 94299
  2. Index ID =0, the database corruption is in “clustered index”, and the data should be repaired using an appropriate “Repair method”
  3. Error Types – consistency errors in Table and consistency errors in database
  4. Suggested Repair Level: Repair_Allow_Data_Loss

Error Inference

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

  1. The corruption was found in Table as well as database
  2. Corruption is in clustered index
  3. Database. 1 and row no. 94299 shows corruption
  4. 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 –

  1. Repairing of corrupt database
  2. No data loss during repair process
  3. 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:

  1. Backup to be performed with automatic full backup
  2. 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.

The 4-step SQL Database repair-process of Select à Scanà Repair à Save lets the user perform the database-repair function with efficiency and recover the repaired database.

Epilogue

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. 

Share with Friends
FacebookTwitterGoogle+LinkedInEmail
Use Cybytes and
Tip the Author!
Join
Share with Friends
FacebookTwitterGoogle+LinkedInEmail
Ready to share your knowledge and expertise?
5 Comments
  1. Thanks for this helpful information, here are some do’s and don’ts to prevent SQL database corruption: https://www.cybrary.it/0p3n/donts-dos-prevent-database-corruption-sql-server/

    Please take a look.

  2. Great Article~

    Thanks for the contribution I found the info exciting and informative.

  3. 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.

Comment on This

You must be logged in to post a comment.

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.

Cybrary On The Go

Get the Cybrary app for Android for online and offline viewing of our lessons.

Get it on Google Play
 

Support Cybrary

Donate Here to Get This Month's Donor Badge

 
Skip to toolbar

We recommend always using caution when following any link

Are you sure you want to continue?

Continue
Cancel