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.
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 ]
}
]
]
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.