How to Use DBCC CHECKDB Repair Options to Repair Database

What Does DBCC CHECKDB Do

DBCC CHECKDB is used to check the physical and logical integrity of a SQL Server database or Azure SQL Database, and is typically used to repair database corruption. It checks objects such as tables, views, clusters, sequences, indexes, and synonyms, and can execute repair options such as repairing or reindexing objects, and rebuilding or reorganizing indexes.

DBCC CHECKALLOC is a command in SQL Server that checks the consistency of disk space allocation structures on the selected database. It verifies that the database's allocation structures, such as the page allocation map and the allocation bitmap, are correct and consistent.
DBCC CHECKTABLE is a command used to check the integrity of all tables and views in a database. It scans each table and view to ensure that the data and indexes are consistent and correct, helping to prevent data corruption and errors.
Run DBCC CHECKCATALOG on the selected database to check for catalog consistency, ensuring the database is online beforehand.
Verify the contents of every indexed view in the specified database.
Verify link-The level of consistency between table metadata, file system directories, and files when saving varbinary(max) data in the file system using FILESTREAM should be ensured. This consistency is crucial to maintain the integrity of the data and ensure that the data is correctly associated with the corresponding metadata and directory structure.
Verify the Service Broker data in the database.

The DBCC CHECKDB command can be run alone, eliminating the need to run separate DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG commands.

How to Use DBCC CHECKDB to Repair Database in SQL Server

To fix a corruption issue in a SQL database, run the following command: `ALTER DATABASE [Database Name] SET RECOVERY SIMPLE;` followed by `ALTER DATABASE [Database Name] SET RECOVERY FULL;`. This will put the database in a temporary recovery mode and then back to the original recovery mode.

[ ( 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

If you don't specify the database name or ID, or if you specify 0, the command will be run on the current database by default.

NOINDEX

The NOINDEX option prevents intensive checks of nonclustered indexes for user tables, reducing the total execution time. However, it does not affect system tables, as integrity checks are always performed on their indexes.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

DBCC CHECKDB is a SQL Server tool that checks the integrity of a database. When it finds errors, it offers repair options to fix them. The repair options are: REPAIR PRIMARY, REPAIR SECONDARY, and REPAIR_REBUILD.

If you use the DBCC CHECKDB repair option, it will attempt to fix errors found, but may result in data loss if successful.
The `REPAIR_FAST` option is used to specify a repair action when a database is in a consistent state but has logical corruption. This option does not perform any repair action, but rather maintains syntax for backward compatibility only. It is not recommended to use this option as it does not provide any actual repair functionality.
If you choose the REPAIR_BUILD option, repair actions will be performed without any data loss, and it includes both 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.

When DBCC CHECKDB reports errors, the recommended action is to restore the database from the last known good backup. If a backup is not available, the REPAIR_ALLOW_DATA_LOSS option can be used as a last resort, but it should not be considered an alternative to restoring from a backup. (Note: This response is a rewritten version of the original text in a concise and one-paragraph format)

How to Repair Database Without CHECKDB REPAIR Option

While REPAIR_ALLOW_DATA_LOSS is often the only choice when a database cannot be repaired from a backup, it's not the only option. If DBCC CHECKDB reports errors on the selected database, you can also use SQL database recovery software, such as Qiling MS SQL Recovery, to repair the corrupted database. This software allows you to recover your database without having to choose the data loss option.

Repair SQL Server database: both primary (.mdf) and secondary (.ndf)
Repair database log files that may result in database errors
A SQL server database repair tool can fix corrupted database objects, including tables, triggers, indexes, keys, rules, and 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 on "Browse" (the two dots) or "Search".
After selecting the file, click the "Repair" button to initiate the analysis process.

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

Step 2: Repair the corrupted database

The software displays all recoverable items in a tree-like structure, with items shown in a left pane.
Click the "Export" button to select the desired component to be recovered from the window.

Step 3: Export to the database or as scripts

To export database objects, you can choose to export them to a database or export them as scripts. This allows you to create a copy of your database schema and structure, or to generate SQL scripts that can be used to recreate the database on another system.
If you choose "Export to database", enter the required information and select the target database.

A window will appear, asking you to enter your login credentials to connect to the server and specify the destination to save the recovered items. To initiate the repair process, simply click "OK".

Note: To resolve the issue, you need to restart the SQL Server service after clicking "OK".

The Bottom Line

DBCC CHECKDB is the typical choice for users to repair their SQL Server database. However, if it fails or you prefer not to use its repair option, REPAIR_ALLOW_DATA_LOSS, Qiling MS SQL Recovery can be used as an alternative to repair the database.