News We Recently Launched AD Migrator and AD Reporter.

Home » Updates » News » DBCC CHECKDB REPAIR_REBUILD Example to Fix Damaged SQL Database

DBCC CHECKDB REPAIR_REBUILD Example to Fix Damaged SQL Database

  author
Written By Andrew Jackson
Anuraag Singh
Approved By Anuraag Singh
Published On May 27th, 2024
Reading Time 7 Minutes Reading

dbcc checkdb repair_rebuild

This blog is going to discuss the DBCC CHECKDB REPAIR_REBUILD command to repair the damaged SQL Server database objects including table, columns, stored procedures, views, triggers, etc. Along with the command, we will explain it with the help of an example, plenty of images, and additional information like causes, repair modes, etc.

Table Of Content

As we know the database console commands are for the core operations including database health checking & executing recovery operations. However, in the recovery process, the SQL commands offer some modes based on the type of recovery. One mode among the three is repair rebuild DBCC CHECKDB. Let’s proceed to know more about this repair clause.

Also Read: Learn About DBCC CHECKTABLE Command As Well

What is DBCC CHECKDB REPAIR_REBUILD Command?

Well as the name suggests, the Repair Rebuild DBCC CHECKDB command is to repair the database with a clause of rebuilding the entire data that is having damage or corruption issues. This states that users will have to wait for a while as it takes more time than other commands that delete the damaged portion or just fix minor issues. The Repair Rebuild command focuses on removing the issues from its root so that users will not have to face any further complications.

DBCC Repair Options

Also Read: How to Fix Corrupted SQL Server Backup File with No Errors

DBCC CHECKDB (‘database_name’ REPAIR_REBUILD) – Syntax

Let’s, quickly understand the syntax of the entire process that users need to execute when or if their database gets corrupt. Make sure there are no mistakes in these commands as one wrong step can have severe consequences.

  1. The first step for users is to put the database into Single User or Emergency mode. Don’t forget to inform all the users of such a recovery operation. Otherwise, executing recovery 7 other queries simultaneously can have repercussions. The command for putting the DB in Single user mode is:

    ALTER DATABASE db_name SET SINGLE_USER
  2. Now, users can run the repair rebuild command to simply execute the operation for fixing the damaged SQL database files.

    DBCC CHECKDB ('db_name', REPAIR_REBUILD) WITH ALL_ERRORMSGS NO_INFOMSGS
  3. At last, we need to put the database back in multi-user mode to let all users access the database for usual working. Below is the command for the same.

    ALTER DATABASE db_name SET MULTI_USER

DBCC CHECKDB REPAIR_REBUILD Example Steps by Step

Now, that we all are aware of the syntax, it is time to take the help of an example to understand the entire repair rebuild DBCC CHECKDB CMD. Let’s take an instance of the database named VLDB having consistency errors which requires repair rebuild mode for the restoration operation. The error message is mentioned below:

error message in SQL

Now, follow the steps mentioned to get the results with ease.

Step-1. Launch SQL Server Management Studio (SSMS) & then enter the following command:

ALTER DATABASE VLDB SET SINGLE_USER

single user mode

Step-2. After setting the DB in single-user mode, Right-Click on Databases & Hit the Refresh button.

refresh databases

Step-3. Simply Expand the Database now, Here, it is visible that the VLDB Database is Set for the Single User or Emergency mode.

single user mode activated

Step-4. Finally, Run the DBCC CHECKDB (‘database_name’ REPAIR_REBUILD) command as shown.

DBCC CHECKDB VLDB REPAIR_REBUILD) WITH ALL_ERRORMSGS NO_INFOMSGS

run command

Step-5. At last, make everything back to normal using the following CMD.

ALTER DATABASE VLDB SET MULTI_USER

When Should We Use the Repair_Rebuild Command Option?

Users must know when to use the DBCC CHECKDB REPAIR_REBUILD command. It’s not like they can use this command for almost all kinds of repair operations. There are a few factors that users need to keep in mind before they proceed to execute this rebuild query.

  • This command is suitable when users have major corruption issues in their database files that are causing plenty of errors.
  • Whenever users need to completely build the damaged data from scratch, they must use this rebuild clause. Other modes are not capable of this.
  • This option takes a lot of time than usual. Therefore, if users have enough time for the data restoration, then only we recommend this mode.

Alternative to DBCC CHECKDB REPAIR_REBUILD CMD

What if the command line method fails? Also, what If this Repair_Rebuild CMD doesn’t work? What if this & what if that!! There might be scenarios where users will not be able to get the desired results using this CMD. Therefore, we have an advanced SQL Database Recovery Software in the field. This advanced solution can easily provide users with the best solution as they want without any technical commands.

Download Now Purchase Now

Download the software & then follow the four basic steps as mentioned below:

Step-1. Open the Tool & Add MDF Files from the system.

add MDF File

Step-2. Choose the Scan mode as Quick or Advanced.

select scan mode

Step-3.Select the Destination Platform to save the files.

set destination

Step-4. Hit the Export/Save button to complete the task.

export or save

What Are All the Repair Modes of this Command?

We have seen the DBCC CHECKDB REPAIR_REBUILD example but some users might not be aware that there are other options as well. Let’s understand about all the options of this command.

In total, users can repair Corrupted SQL Database with this command three main options:

  • Repair_Fast – The repair fast CMD is mainly for the quick results. Evidently, it only focuses on the minor issues.
  • Repair_Allow_Data_Loss – This DBCC CHECKDB Repair_Allow_Data_Loss option is a bit risky as there is a chance that users might lose their data in the process of restoration.
  • DBCC CHECKDB Repair_Rebuild – This is the safest option if users have no option but to execute manual commands. Otherwise, automated solutions are better.

Causes & Prevention of SQL Corruption to Avoid Repair Commands

Before we end this article, it’s time that we understand the SQL database corruption causes to prevent such issues in the future. It is even better to prevent database damage than to use the DBCC CHECKDB REPAIR_REBUILD command to fix the corrupt database. 

Therefore, below are some common causes & their prevention tips:

Sudden Shutdown of the System & Application: Abrupt system shutdowns often cause query interruption which ultimately results in data loss or corruption. To prevent this, it is highly suggested that users should have power backups & regular system maintenance.

Software & Hardware Issues: Software issues like old versions, & hardware issues like a bad sector in the disk, etc are most common that contribute to database corruption. Proper monitoring of the database can help in preventing damage & identify the issues timely.

Virus & Attacks: Some trendy & common causes for corruption are SQL injection attacks, malware, viruses, etc. The prevention measure for this is to have multi-layer security for databases including physical security, firewalls, and anti-viruses.

Conclusion

We have mentioned DBCC CHECKDB REPAIR_REBUILD with example in this article having step by step tutorial. This can help users execute their tasks with utmost precision. Moreover, with the help of the exact syntax, other repair modes, causes & prevention, users can pretty much tackle all kinds of SQL DB corruption situations with ease.

  author

By Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management, etc. I love to share my knowledge with SQL Geeks.