How to Repair SQL Database using DBCC CHECKDB Command

Summary: Database Console Command (DBCC) CHECKDB is frequently used by many SQL Server database administrators (DBAs) to identify errors in the database. Also, DBCC CHECKDB comes with three repair options to fix database-related errors. This article will outline how you can use DBCC CHECKDB to repair SQL database. Also, it will discuss about a specialized MS SQL repair software you can use when DBCC CHECKDB fails to repair the database.  

As database administrators (DBAs), we all know how important it is to run ‘Database Console Command (DBCC) CHECKDB’ regularly to check the physical and logical integrity of database objects, index relationships, and other structure checks. The failure of any of these checks will report consistency errors as part of the database console command.

The best method to repair errors in the database, reported by DBCC CHECKDB, is to run the last known good backup as recommended by Microsoft. However, if the backup is not available (or the backup is damaged), you can try accessing the database in Emergency state.

The Emergency state allows access to database that is in Suspect mode. It also allows running DBCC CHECKDB repair options to resolve database corruption.

How to use DBCC CHECKDB for Repairing SQL Database?

Download and install ‘SQL Server Management Studio (SSMS)’ and follow these steps to repair SQL database by using DBCC CHECKDB:

NOTE: In below-mentioned steps, we will be using database_name as Dbtesting. Make sure to replace DBtesting with the name of your database.

Step 1: Set database to emergency mode

Change the database status to EMERGENCY mode, which provides a read-only access to the administrator. To put the database in EMERGENCY mode, run following query in SSMS:

Step 2: Check for corruption errors

Once the administrator is able to access the database, execute the following DBCC CHECKDB command to analyze corruption errors in the database:

If DBCC CHECKDB detects any errors in the database, it will recommend appropriate repair options to fix the issue.

Step 3: Set SQL server database to single-user mode

Before using the DBCC CHECKDB repair options, put the corrupt database in single-user mode so as to prevent other users from modifying the data during the repair process. To set SQL database mode to SINGLE_USER, follow these methods:

Method 1: Using Graphical User Interface (GUI)

  • Right-click the database you want to repair, and then click Properties
  • In Database Properties window, click Options.
  • Click Restrict Access drop-down box under the State tab, select SINGLE_USER option, and then click OK.

Method 2: Using Transact-SQL (T-SQL) Commands

You can also set the database to SINGLE_USER mode, by running the following T-SQL query in SSMS:

Step 4: Repair the Database

Once you have changed the database mode to SINGLE_USER, run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS repair option to repair SQL server database:

NOTES:

  1. Besides REPAIR_ALLOW_DATA_LOSS repair mode, DBCC also offers two other repair modes:
    • REPAIR_FAST: This repair option does not perform any repair actions. It helps maintain syntax for backward compatibility. 
    • REPAIR_REBUILD: The REPAIR_REBUILD option helps repair the database without any data loss. It can be used to repair missing rows in nonclustered indexes, and for rebuilding an index.
  2. While the REPAIR_ALLOW_DATA_LOSS repair option helps in repairing all reported errors in the SQL server database, it is not the best option for repairing database corruption. This repair option causes data loss. In fact, Microsoft recommends using the REPAIR_ALLOW_DATA_LOSS option as a last resort when you cannot restore a database from the backup. If you do not have a backup and cannot risk losing data, use a specialized MS SQL repair software to repair the database without any loss in database integrity.

Step 5: Set Database back to MULTI_USER Mode

After successfully repairing the database, set the database to MULTI_USER mode by executing the following command:

What else you can do when DBCC CHECKDB fails?

Running the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command might require deleting some database pages, resulting in loss of data. Also, DBCC CHECKDB commands might fail and return an error when dealing with severely corrupt SQL database files.

Use Stellar Repair for MS SQL software to repair severely corrupt MS SQL database and restore all its components, while maintaining database integrity. The SQL Recovery Software helps reinstate access to the database with minimal manual efforts and time.

Key Features:

  • Repairs both MDF and NDF database files
  • Recovers all database components including tables, keys, indexes, stored procedures, etc.
  • Allows recovery of deleted records
  • Recovers SQL tables with PAGE and ROW compression
  • Supports selective recovery of database objects
  • Previews recoverable database objects

To know the complete working process of the software, watch the video

Conclusion

DBCC CHECKDB can help check consistency errors and corruption in MS SQL database. A corrupt database could lead to unnecessary downtime and data loss. To overcome database-related errors, restore the database from the most recent backup.

If current database backup does not exist or if the backup itself is corrupt, you can use DBCC CHECKDB ‘REPAIR_ALLOW_DATA_LOSS’ repair option to fix the database. But, this repair option involves risk of data loss. Also, DBCC CHECKDB might fail to fix the issue.

Use Stellar Repair for SQL software to repair MS SQL database and recover all its components with precision, maintaining database structure and integrity of database objects.