Steps to Use DBCC CHECKDB To Repair SQL Database

Administrators use the Database Console Command (DBCC) to check the physical and logical integrity of the objects in the Microsoft SQL Server Databases. The commands used for this purpose include:

DBCC also resolves the SQL database corruption issues by performing the necessary repair operations. But, if DBCC CHECKDB cannot repair the SQL database corruption, then third-party SQL database repair tool would be helpful.

Repairing SQL database with DBCC CHECKDB

Open SQL Server Management Studio and follow the steps mentioned here to repair your corrupt database with the help of DBCC CHECKDB command. Before that, ensure you have administrative privileges and MS SQL Server Management Studio (SSMS) on your PC or laptop.

Please note that in the database repair steps mentioned below, we will use SQL_TEST_DB as our database name. You can replace it with your database when doing the repair process.

1. Set your Database status to EMERGENCY mode

Run the query given below in SSMS to set the database in EMERGENCY mode:


This will provide you (administrator) with read-only access to the database.

You can check the status of your database by running this command in the SSMS query window.

 SELECT name, state_desc  FROM sys.databases

Alternatively, you can also check the status of your database by going through the following easy steps:

2. Check your database for corruption errors

Next, you need to find out the SQL database corruption errors in the database by executing the DBCC CHECKDB command.


3. Set the corrupt database in SINGLE_USER mode

You can do this either with the help of SSMS Graphical User Interface or by executing Transact-SQL (T-SQL) Commands.

i. Set SINGLE_USER mode By GUI:

ii. Set SINGLE_USER mode By SQL Command

Alternatively, you can run the following command in the Query window.


4. Repair your SQL Server Database

Now is the time to repair your corrupted database. Since you have already set the database to SINGLE_USER mode, you can run CHECKDB DBCC command with the option of REPAIR_ALLOW_DATA_LOSS. Here is the command.



You may use several options with REPAIR_ALLOW_DATA_LOSS as given in the syntax below:


Let’s learn the options one by one.

REPAIR_ALLOW_DATA_LOSS:  Repairs all the errors in the database but can lead to some data loss.

REPAIR_FAST: Helps to maintain syntax for backward compatibility without performing any repair task.

REPAIR_REBUILD: Performs time-consuming repairs such as index rebuilding. At times, it can also perform quick repairs like repairing misplaced rows in non-clustered indexes. It does not repair errors related to FILESTREAM data.

Please note that REPAIR_ALLOW_DATA_LOSS may not always be the best method to repair a database. This is because, despite successful database repair, this option can lead to significant data loss. It should be the last emergency option in the absence of any backup for database restoration. It is better to check the referential integrity of the database with the help of DBCC CHECKCONSTRAINTS option after using REPAIR_ALLOW_DATA_LOSS.

5. Change the mode of your repaired database to MULTI_USER

After repairing your corrupted database successfully with the help of REPAIR_ALLOW_DATA_LOSS, change the mode to MULTI_USER. Here is the command:


Alternative to DBCC CHECKDB for repairing SQL database corruption

As already mentioned, executing REPAIR_ALLOW_DATA_LOSS command with DBCC CHECKDB might delete several database pages, leading to significant data loss. At times, the DBCC CHECKDB might not be able to solve the SQL database corruption issue, and return error. Using an advanced SQL database repair tool, such as Stellar Repair for MS SQL would be suitable in this regard.

It offers a wide range of features:

Repairing SQL database corruption through Stellar Repair for MS SQL

Here are the steps to recover a corrupted SQL database with the help of this SQL database repair tool

  1. Download the .exe file SQL database repair tool from the Stellar Info website
  2. Install and launch the software on your PC or laptop

3. Click Select Database.

4. In the ‘Select Database’ dialog box, click the Browse button to choose the database that needs repair

5. Select from the ‘Standard Scan’ or ‘Advanced Scan’ options.

6. On successful repair, you will see the message, ‘Select MS SQL database repaired successfully’.

7. Preview the objects of the recovered SQL database

8. After verifying the data, Save the file by clicking FileàSave

9. Save the repaired database in any of the given file formats including MDF, CSV, XLS, or HTML.

10. Click Next.

11. Enter connection details to SQL Server instance. Click Next.

12. Choose from among Fast Saving or Standard Saving modes to save the recovered SQL database. Click Save.

13. Next, you will see the saving status of the tables as Completed, Processing, Pending, or Aborted.

14. Once saved, you will get the ‘File Saved Successfully’ message.

15. You can find the recovered database in the saved location.

Final Words

Administrators often use DBCC CHECKDB command to deal with SQL database corruption. The options to check the logical and physical integrity of the SQL database include DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG. Likewise, the options to repair the database in combination with REPAIR_ALLOW_DATA_LOSS include REPAIR_FAST and REPAIR_REBUILD among others.

At times, the manual method may give errors or fail to resolve the problem of SQL database corruption. To remedy this situation, admins prefer using an SQL database repair tool loaded with multiple features, it ensures quick and hassle-free recovery of the database. Moreover, it ensures no data loss during the recovery process.

To know more on how to tackle the issue of SQL database corruption through Stellar Repair for MS SQL, feel free to contact Stellar Info. You can get ample information about the SQL database repair tool from the Stellar Info website itself.

Exit mobile version