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.
| | DBCC CHECKDB | Stellar Repair for MS SQL |
| Ease-of-use | Requires knowledge and skills to use DBCC CHECKDB commands for successful repair process. | Easy-to-use user interface that does not require any technical skills to perform the repair process. |
| Risk of data loss | Using DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS option may result in data loss. | Recovers all the data, such as tables, triggers, indexes, etc., from corrupt database with complete integrity. |
| | | |
| Process time | May require to run the DBCC CHECKDB command multiple times to completely repair the database. It can be time-consuming. | It only requires a few steps to repair the SQL database. |
| Operating system compatibility | Supports Windows and Linux operating systems. | Supports Windows and Linux operating systems. |
| Cost | DBCC CHECKDB commands are free to use. | Paid repair tool with advanced features. |
| Preview of recoverable objects | No option to preview the recoverable objects. | Comes with an option to preview the recoverable objects before saving. |
| Data integrity | There is no guarantee of 100% accuracy of the repaired data. | Maintains complete data integrity of the recovered data. |
| File saving formats | No multiple file saving options. | Option to save the repaired database in a new database, live database, and other formats like HTML, XLS, SQL Script, etc. |
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.
