How to Use DBCC CHECKDB Repair Options to Repair Database

What Does DBCC CHECKDB Do

DBCC CHECKDB, also known as Database Console Command CHECKDB, is used to check both physical and logical integrity of objects (like tables, views, clusters, sequences, indexes, and synonyms) in a SQL Server database or Azure SQL Database. It is generally used to repair database corruption. When you run DBCC CHECKDB, you are actually executing the repair options below:

Run DBCC CHECKALLOC: Check the consistency of disk space allocation structures on the selected database.
Run DBCC CHECKTABLE: Check the integrity of all the table and view.
Run DBCC CHECKCATALOG: Checks for catalog consistency within the selected database. The precondition for running this command is that the database must be online.
Verify the contents of every indexed view in the specified database.
Verify link-level consistency between table metadata, file system directories, and files when saving varbinary(max) data in the file system using FILESTREAM.
Verify the Service Broker data in the database.

Thus, it is unnecessary to run DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG separately from DBCC CHECKDB.

How to Use DBCC CHECKDB to Repair Database in SQL Server

When you want to fix a corruption issue in a SQL database, run the syntax below:

[ ( database_name | database_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 ]
}
]
]

You may want to know:

database_name | database_id | 0

Is the name or ID of the database for which to run DBCC CHECKDB repair. If not specified, or if 0 is specified, the command will be applied to the current database by default.

NOINDEX

Means that intensive checks of nonclustered indexes for user tables should not be executed. This reduces the total execution time. NOINDEX won't affect system tables because integrity checks are always performed on system table indexes.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

When you use one of the repair options above, you allow DBCC CHECKDB to repair the found errors. To understand the difference between these repair options:

REPAIR_ALLOW_DATA_LOSS: If you use this repair option, DBCC CHECKDB will attempt to repair the errors found. It may CAUSE DATA LOSS if performed successfully.
REPAIR_FAST: This repair will not perform any repair action. Instead, it only maintains syntax for backward compatibility only.
REPAIR_BUILD: If you choose this repair option. Repair actions are performed but no data loss will occur. REPAIR_BUILD includes two kinds of repairs: quick repair and deep repair.

 Notice:: For more information about other items in the syntax, you can refer to the related documents in Microsoft.com.

However, as Microsoft suggests, use the REPAIR options only as a last resort. Why? When there are errors reported by DBCC CHECKDB, the optimal option recommended is to restore the database from the last known good backup. As REPAIR_ALLOW_DATA_LOSS is not an alternative for restoring from a backup, it is only recommended when there is no backup available.

How to Repair Database Without CHECKDB REPAIR Option

Is REPAIR_ALLOW_DATA_LOSS the only choice when you cannot repair your database from a backup? Actually, it's not. If DBCC CHECKDB reports errors on the selected database, you can repair your corrupted database using the SQL database recovery software - Qiling MS SQL Recovery. You can apply this software to:

Repair SQL Server database: both primary (.mdf) and secondary (.ndf)
Repair database log files that may result in database errors
Repairs corrupted SQL server database objects - tables, triggers, indexes, keys, rules & stored procedures
Recover deleted/dropped SQL database records

Free Download
Windows 11/10/8/7, 100% Secure

To repair a database:

Step 1: Select the corrupted database for recovery

Launch Qiling MS SQL Recovery.
Select the corrupted database file by clicking "Browse" (the two dots) or "Search".
After selecting the file, click the "Repair" button to start the Analyzing process.

Note: To use this software, you need to stop the SQL Server service.

Step 2: Repair the corrupted database

The software displays all the recoverable items in a tree-like structure. The items are shown in a left pane.
Select the desired component to be recovered. From the window, click the "Export" button.

Step 3: Export to the database or as scripts

Choose to export the database objects to database or export the items as scripts
If you choose "Export to database", enter the information required and choose the target database.

A window appears up asking you to provide credentials to connect to the server and the destination to save the recovered items. In order to begin the repairing process, click "OK".

Note: Before clicking "OK", you need to restart the SQL Server service.

The Bottom Line

DBCC CHECKDB is the common choice for users to repair their database in SQL Server. However, it is not the only choice. If DBCC CHECKDB fails to work or you don't want to use the repair option - REPAIR_ALLOW_DATA_LOSS, you can use Qiling MS SQL Recovery to repair your database as alternative choice.