How to Repair SQL Database using DBCC CHECKDB Command?

Priyanka Chauhan | Updated on July 15, 2024 SQL Database Repair

Summary: You can use the DBCC CHECKDB command in SQL Server to check and repair corrupt SQL database. This posts explains how to use the DBCC CHECKDB command to repair the SQL database. It also mentions a specialized MS SQL repair software that can repair corrupt SQL database in just a few simple steps and without any data loss.

Database Console Command (DBCC) commands are used for database management and administration in SQL Server. The DBCC CHECKDB command is used to check the logical and physical integrity of SQL database. It helps identify and resolve corruption-related and structural issues in the SQL database.

How the DBCC CHECKDB Command Works?

The command thoroughly scans the database and performs integrity checks and other structural checks. If any of these checks fail, it displays consistency errors indicating issues in the database and also recommend appropriate repair option. Let’s take a look at the syntax of DBCC CHECKDB command.

DBCC CHECKDB    
    [ ( db_name | db_id | 0   
        [ , NOINDEX    
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]   
    ) ]   
    [ WITH    
        {   
            [ ALL_ERRORMSGS ]   
            [ , EXTENDED_LOGICAL_CHECKS ]    
            [ , NO_INFOMSGS ]   
            [ , TABLOCK ]   
            [ , ESTIMATEONLY ]   
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]   
            [ , MAXDOP  = number_of_processors ]   
        }   
    ]   
]Copy Code

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 is corrupted, you can try accessing the database in Emergency state.

The Emergency state allows accessing a database marked as suspect. It also allows running DBCC CHECKDB repair options to resolve database corruption. Once the database becomes accessible, repair it using the minimum level of repair option.

Note: Repair operations exclude any constraints applied to or between tables. So, if any of the table has one or more constraints, you must run DBCC CHECKCONSTRAINTS following a repair operation.

How to Use DBCC CHECKDB Command?

Before using DBCC CHECKDB, let’s look at its syntax.

Syntax:

DBCC CHECKDB    
    [ ( db_name | db_id | 0   
        [ , NOINDEX    
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]   
    ) ]   
    [ WITH    
        {   
            [ ALL_ERRORMSGS ]   
            [ , EXTENDED_LOGICAL_CHECKS ]    
            [ , NO_INFOMSGS ]   
            [ , TABLOCK ]   
            [ , ESTIMATEONLY ]   
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]   
            [ , MAXDOP  = number_of_processors ]   
        }   
    ]   
]

Now, let’s understand the various options used in the above DBCC CHECKDB command.

database_name | database_id | 0: Specifies the name or ID of the database against which you need to run integrity checks. If the ‘database_name’ or ‘id’ is not specified and ‘0’ is specified, the current database will be used by default.
NOINDEX: Performs only logical checks to reduce the total execution time. It does not include non-clustered indexes in the checks.
REPAIR_FAST: This option does not perform any repair actions. It helps maintain syntax for backward compatibility.
REPAIR_REBUILD: Helps repair database without any data loss. It can be used to repair missing rows in non-clustered indexes and for rebuilding an index.
REPAIR_ALLOW_DATA_LOSS: This option tries to fix all the reported issues and errors. Use this option as a last resort as it can lead to data loss.
ALL_ERRORMSGS: This argument displays all the error messages for each object.
EXTENDED_LOGICAL_CHECKS: Use this option to perform additional checks.
NO_INFOMSGS: DBCC output displays informational messages that are not related to the consistency errors. This option turns off the informational messages.
TABLOCK: Uses locks rather than internal database snapshot to perform consistency checks on the database.
ESTIMATEONLY: Specifies the estimated space required by the ‘tempdb’ database for executing the CHECKDB command.
PHYSICAL_ONLY: Limits consistency checks on the physical structure of the database page, reducing runtime for DBCC CHECKDB on large databases.
DATA_PURITY: Helps check database for invalid or out-of-range column values.

Steps to Repair SQL Database using the DBCC CHECKDB Command

To run the DBCC CHECKDB command, make sure you have the administrative privileges. Then, open the SQL Server Management Studio (SSMS) and follow these steps:

Note: 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

If the database is inaccessible, first change the database status to EMERGENCY mode. This will provide read-only access to the administrator. To put the database in EMERGENCY mode, run the following query in SSMS:

ALTER DATABASE [Dbtesting] SET EMERGENCY

Step 2: Check Database for Corruption Errors

After setting the database to EMERGENCY mode, execute the following command to check the database for corruption errors:

DBCC CHECKDB (Dbtesting)

If the DBCC CHECKDB command detects any errors or corruption in the database, it will recommend an appropriate repair option.

Step 3: Set Database to SINGLE_USER Mode

Before using the repair option, you need to put the database in SINGLE_USER mode to prevent other users from modifying the data during the repair process. To set the database to SINGLE_USER mode, run the following T-SQL query in SSMS:

ALTER DATABASE Dbtesting SET SINGLE_USER

Step 4: Repair the Database

After setting the database to SINGLE_USER mode, run the command with the REPAIR option recommended by DBCC CHECKDB command. If it shows an error message recommending to run the REPAIR_REBUILD as the minimum repair level, then run the DBCC CHECKDB command with REPAIR_REBUILD option as given below:

DBCC CHECKDB (' Dbtesting ', REPAIR_REBUILD)
GO

This command will rebuild the database without any data loss. It can repair missing rows in non-clustered indexes and help in fixing minor corruption errors. However, it is a time-consuming option.

Alternatively, you can use the REPAIR_FAST with the command as given below:

DBCC CHECKDB (' Dbtesting ', REPAIR_FAST)
GO

This repair option only maintains backward compatibility syntax and does not perform any repair actions.

If the above repair options fail or the DBCC CHECKDB command recommended using the REPAIR_ALLOW_DATA_LOSS repair option, then run the DBCC CHECKDB command as given below:

DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 
GO

The REPAIR_ALLOW_DATA_LOSS repair option helps in repairing all reported errors in the SQL server database but it causes data loss. In fact, Microsoft recommends using the REPAIR_ALLOW_DATA_LOSS option as a last resort.

Step 5: Set Database to MULTI_USER Mode

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

ALTER DATABASE Dbtesting SET MULTI_USER

Downsides of DBCC CHECKDB Command

Though the DBCC CHECKDB command can fix database consistency issues, you have to consider the following downsides when using the command with the REPAIR_ALLOW_DATA_LOSS option:

It may deallocate rows or pages when repairing the database. Deallocated data can sometimes become unrecoverable.
It may leave your database in a logically inconsistent state.
You may require to use this command multiple times to fix all errors associated with SQL database. It is a time-consuming process.
It does not guarantee complete data recovery.

An Alternative to DBCC CHECKDB Command

To overcome the downsides of the DBCC CHEKDB command and repair the corrupt SQL database with complete integrity, you can use a specialized MS SQL repair software, such as Stellar Repair for MS SQL. The software repairs severely corrupt MS SQL database and restores all its components. 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
Offers preview of recoverable database objects before saving
Saves the repaired database to a new or live database and formats like CSV, HTML, and XLS
Supports SQL Server database 2022, 2019, 2017, 2016, 2014, 2012, and lower versions
Compatible with both Windows and Linux operating systems

Conclusion

If your SQL database is corrupted, you can use the DBCC CHECKDB command to check and repair it. Above, we have explained how to use the DBCC CHECKDB command to repair SQL database. However, using the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS involves risk of data loss. To repair the corrupt database without data loss, you can use a specialized MS SQL repair software such as Stellar Repair for MS SQL. The software helps retrieve all the SQL database components, including tables, deleted table records, indexes, views, etc. You can free download the software to evaluate its functionality and efficacy.