Summary: Database Console Command (DBCC) CHECKDB is used to identify errors in the SQL Server database. The DBCC CHECKDB comes with three repair options to fix database-related errors. This article outlines how to use DBCC CHECKDB command to repair SQL database. Also, it will discuss about a specialized MS SQL repair software you can use when DBCC CHECKDB fails to repair the database.
DBCC CHECKDB is used to check the physical and logical integrity of database objects, index relationships, and other structural checks. The failure of any of these checks will report consistency errors as part of the database console command.
The best method to repair errors in the database, reported by DBCC CHECKDB, is to run the last known good backup as recommended by Microsoft. However, if the backup is not available or is corrupted, you can try accessing the database in Emergency state.
The Emergency state allows accessing a database marked as suspect. It also allows running DBCC CHECKDB repair options to resolve database corruption. Once the database becomes accessible, repair it using the minimum level of repair option.
Note: Repair operations exclude any constraints applied to or between tables. So, if any of the table has one or more constraints, you must run DBCC CHECKCONSTRAINTS following a repair operation.
Before using DBCC CHECKDB, let’s look at its syntax.
Syntax:
| DBCC CHECKDB [ ( db_name | db_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 ] } ] ] |
There are a few options you can use to perform database consistency checks using DBCC CHECKDB. These options are as follows:
You must ensure that your system meets the following prerequisites before you run DBCC CHECKDB to perform consistency checks on a database or repair it.
Contents
Open SSMS and follow these steps to repair SQL database by using DBCC CHECKDB:
Note: In below-mentioned steps, we will be using database_name as Dbtesting. Make sure to replace DBtesting with the name of your database.
Change the database status to EMERGENCY mode, which provides a read-only access to the administrator. To put the database in EMERGENCY mode, run the following query in SSMS:
ALTER DATABASE [Dbtesting] SET EMERGENCY
Once the administrator is able to access the database, execute the following DBCC CHECKDB command to analyze corruption errors in the database:
DBCC CHECKDB (Dbtesting)
If DBCC CHECKDB detects any errors in the database, it will recommend appropriate repair options to fix the issue.
Before using the DBCC CHECKDB repair options, put the corrupt database in SINGLE_USER mode to prevent other users from modifying the data during the repair process. To set SQL database mode to SINGLE_USER, follow these methods:
Method 1: Using Graphical User Interface (GUI)
Open SSMS and perform these steps to set the database to SINGLE_USER mode:
Method 2: Using Transact-SQL (T-SQL) Commands
You can also set the database to SINGLE_USER mode, by running the following T-SQL query in SSMS:
ALTER DATABASE Dbtesting SET SINGLE_USER
Once you have changed the database mode to SINGLE_USER, run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS repair option to repair SQL server database.
DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO
Note: While the REPAIR_ALLOW_DATA_LOSS repair option helps in repairing all reported errors in the SQL server database, it is not the best option for repairing database corruption. This repair option causes data loss. In fact, Microsoft recommends using the REPAIR_ALLOW_DATA_LOSS option as a last resort when you cannot restore a database from the backup. If you do not have a backup and cannot risk losing data, use a specialized MS SQL repair software to repair the database without any loss in database integrity.
After successfully repairing the database, set the database to MULTI_USER mode by executing the following command:
ALTER DATABASE Dbtesting SET MULTI_USER
Running the DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS command might require deleting some database pages, resulting in loss of data. Also, DBCC CHECKDB commands might fail and return an error when dealing with severely corrupt SQL database files.
In such a case, use a specialized MS SQL repair software, such as Stellar Repair for MS SQL. The software repairs severely corrupt MS SQL database and restores all its components, while maintaining database integrity. The SQL recovery software helps reinstate access to the database with minimal manual efforts and time.
Key Features:
To know the complete working process of the software, watch the video
A corrupt database could lead to unnecessary downtime and data loss. To overcome database-related errors, restore the database from the most recent backup. If current database backup does not exist or if the backup itself is corrupt, you can use DBCC CHECKDB ‘REPAIR_ALLOW_DATA_LOSS’ repair option to fix the database. But, this repair option involves risk of data loss. Also, DBCC CHECKDB might fail to fix the issue.
Use Stellar Repair for SQL software to repair MS SQL database and recover all its components with precision, maintaining database structure and integrity of database objects.
Priyanka is a technology expert working for key technology domains that revolve around Data Recovery and related software's. She got expertise on related subjects like SQL Database, Access Database, QuickBooks, and Microsoft Excel. Loves to write on different technology and data recovery subjects on regular basis. Technology freak who always found exploring neo-tech subjects, when not writing, research is something that keeps her going in life.
