How to Repair Corrupt SQL Database using DBCC CHECKDB Command?

MS SQL databases are susceptible to corruption and integrity issues. Corruption in MS SQL database can occur due to various internal and external factors, such as sudden power failure, abrupt system shutdown, hardware failure, lack of storage space, malware/virus infection, and others. Corruption in database can lead to various errors or even inaccessibility of data.

However, to detect and fix corruption in database, SQL Server provides DBCC CHECKDB commands. The DBCC CHECKDB command thoroughly scans the database and checks the integrity of all the objects in the database, such as pages, tables, views, etc. It also detects torn pages, checksum failures, and other issues in the database. It helps identify and resolve corruption-related issues, page-level corruption issues, and structural issues in the SQL database.

In this article, we’ll see how to repair SQL database using the DBCC CHECKDB command. We’ll also mention an advanced SQL repair software that can help you quickly repair corrupt SQL database with complete integrity.

Process to Use DBCC CHECKDB Command to Repair Corrupt SQL Database

The DBCC CHECKDB command performs integrity and other structural checks on the database. In case of failure of any of the checks, the command shows consistency errors and also recommend an appropriate option to repair the database. Here are the stepwise instructions to check and repair SQL database using DBCC CHECKDB.

First, you need to execute the DBCC CHECKDB command as given below to check the database for corruption.

DBCC CHECKDB ‘database_name’;

If the database is inaccessible, then change its status to EMERGENCY mode. This mode enables the read-only access to the administrator. To change the database to EMERGENCY mode, run the below command:

ALTER DATABASE [database_name] SET EMERGENCY

After changing the database status to EMERGENCY mode, execute the DBCC CHECKDB command again. If the command displays consistency errors, you need to repair the database with the repair option recommend by the command.

To repair the database, first set it to the SINGLE_USER mode to stop other users from modifying data during the repair process. For this, run the below query:

ALTER DATABASE database_name SET SINGLE_USER

Then, run the repair option recommended by the DBCC CHECKDB command. It will be REPAIR_REBUILD, REPAIR_FAST or REPAIR_ALLOW_DATA_LOSS.

If the REPAIR_REBUILD option is recommended, run the command as given below. This repair option can rebuild indexes, including the non-clustered indexes.

DBCC CHECKDB (‘Database_name’, REPAIR_REBUILD);

Note: The REPAIR_REBUILD option can help resolve minor corruption in the database. The REPAIR_REBUILD option only maintains syntax for backward compatibility. It does not help resolve complex corruption issues in the database.

To rebuild the database quickly, you can use the REPAIR_FAST option. Here’s the command:

DBCC CHECKDB (' Database_name', REPAIR_FAST)
GO

If the DBCC CHECKDB command has recommended the REPAIR_ALLOW_DATA_LOSS option, then run the command as given below:

DBCC CHECKDB (‘Database_name’, REPAIR_ALLOW_DATA_LOSS);

This repair option reads and uses the suspected pages to resolve corruption issues, which increases the chances of recovering the database. It can resolve all types of corruption errors in the database but may cause data loss. Therefore, it is suggested to use the REPAIR_ALLOW_DATA_LOSS option as the last resort to repair the database.

Limitations of DBCC CHECKDB Command

• The DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option may deallocate rows or pages in the SQL database to resolve the issue. Deallocated data can sometimes become unrecoverable.
• You may need to use the DBCC CHECKDB command repeatedly to fix all errors in the database, which is time-consuming.
• Sometimes, the command can cause logical inconsistencies in the database.
• It does not recover complete data in the database.
• Repairing large-sized databases with DBCC CHECKDB command is time-consuming. It can result in extended downtime.

An Alternative to DBCC CHECKDB Command

Although the DBCC CHECKDB command can resolve corruption issues in database, it is not powerful enough to deal with complex corruption issues and can also result in data loss. To overcome such limitations, you can use a third-party SQL repair tool, like Stellar Repair for MS SQL. It is recommended by MVPs and IT experts to repair corrupt SQL database files (both MDF and NDF) quickly and without any data loss. The tool recovers all the objects, like tables, pages, indexes, etc., from the corrupted database and save them to a new database file.

Comparative Analysis: DBCC CHECKDB vs Stellar Repair for MS SQL

Here’s a quick comparison between DBCC CHECKDB and Stellar Repair for MS SQL.

Customers’ Testimonials

Stellar Repair for MS SQL specializes in resolving complex corruption issues in SQL databases. Check the testimonials to discover the prowess of the tool in repairing corrupt SQL databases.

https://www.scarydba.com/2020/09/23/review-stellar-repair-for-sql-server/

Watch this video to know what customers are saying about Stellar Repair for SQL:

https://www.youtube.com/watch?v=yso4axqXxlM

How to Use Stellar Repair for MS SQL to Repair Corrupt Database?

Following are the stepwise instructions to use Stellar Repair for MS SQL to repair corrupt SQL database:

• Download, install, and launch the Stellar Repair for MS SQL software.
• In the Select Database window, click Browse to select the database file (You can use the Find option if you do not know the location of the data file).

• Next, click on the Repair button.
• Once the database is repaired, a Repair Complete window is displayed. Click OK.

• The software shows a preview of the repaired file.
• Select the objects you want to save and click the Save button. You can save the repaired data to a live database, a new database, or other formats, like CSV, HTML, and Excel.

• Select the saving option (for example, New Database) and then click Next.
• Under Connect To Server, fill in the credentials, select the location to save the repaired file, and click Next.
• In the Save Mode window, select the appropriate saving mode and then click Save.
• On the Save Complete window, click on OK.

Refer the video below to know how the software works:

Conclusion

You can use the DBCC CHECKDB command to repair corrupt SQL database. In this article, we have discussed the stepwise process to repair corrupt SQL database using the DBCC CHECKDB command. Alternatively, you can use a professional SQL repair tool, such as Stellar Repair for MS SQL, to repair corrupt SQL database files quickly and without any data loss. This MVP-recommended tool can repair both NDF and MDF files. With this tool, you can repair the database in minimal time and effort, with no complications. You can download the demo version of Stellar Repair for MS SQL to check its functionality.

FAQs

What is the primary function of DBCC CHECKDB?

DBCC CHECKDB is used to repair the corrupt SQL database.

How does Stellar Repair for MS SQL differ from DBCC CHECKDB?

DBCC CHECKDB is a free tool available in SQL Server to repair corrupt database files, whereas Stellar Repair for MS SQL is a third-party SQL repair tool that can even repair severely corrupted SQL database files.

Can Stellar Repair for MS SQL be used in conjunction with DBCC CHECKDB?

No, you can’t use Stellar Repair for MS SQL in conjunction with DBCC CHECKDB. You can use it as an alternate option to repair corrupt SQL database.

Is there any risk of data loss when using Stellar Repair for MS SQL?

There is no risk of data loss when using Stellar Repair for MS SQL. It recovers all the data from the corrupted database file and saves the repaired data in a new database (NDF/MDF) file with complete integrity.

Do I need technical expertise to use Stellar Repair for MS SQL?

The tool offers an easy-to-navigate user interface that doesn’t require any technical expertise.

When should I choose Stellar Repair for MS SQL over DBCC CHECKDB?

If you want to repair the database without any data loss and in minimal time, then you can use Stellar Repair for MS SQL.

Is Stellar Repair for MS SQL compatible with all versions of SQL Server?

Yes, Stellar Repair for MS SQL is compatible with MS SQL Server 2022 and all the earlier versions.