Summary: Database Console Command (DBCC) CHECKDB is frequently used by many SQL Server database administrators (DBAs) to identify errors in the database. Also, DBCC CHECKDB comes with three repair options to fix database-related errors. This article will outline how you can use DBCC CHECKDB 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.
As database administrators (DBAs), we all know how important it is to run ‘Database Console Command (DBCC) CHECKDB’ regularly 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 the backup is damaged), you can try accessing the database in Emergency state.
The Emergency state allows access to database that is in Suspect mode. It also allows running DBCC CHECKDB repair options to resolve database corruption.
How to use DBCC CHECKDB for Repairing SQL Database?
Download and install ‘SQL Server Management Studio (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.
Step 1: Set database to emergency mode
Change the database status to EMERGENCY mode, which provides a read-only access to the administrator. To put the database in EMERGENCY mode, run following query in SSMS:
ALTER DATABASE [Dbtesting] SET EMERGENCY
Step 2: Check for corruption errors
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.
Step 3: Set SQL server database to single-user mode
Before using the DBCC CHECKDB repair options, put the corrupt database in single-user mode so as 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)
- Right-click the database you want to repair, and then click Properties
- In Database Properties window, click Options.
- Click Restrict Access drop-down box under the State tab, select SINGLE_USER option, and then click OK.
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
Step 4: Repair the Database
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:
- Besides REPAIR_ALLOW_DATA_LOSS repair mode, DBCC also offers two other repair modes:
- REPAIR_FAST: This repair option does not perform any repair actions. It helps maintain syntax for backward compatibility.
- REPAIR_REBUILD: The REPAIR_REBUILD option helps repair the database without any data loss. It can be used to repair missing rows in nonclustered indexes, and for rebuilding an index.
- 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.
DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO
Step 5: Set Database back to MULTI_USER Mode
After successfully repairing the database, set the database to MULTI_USER mode by executing the following command:
ALTER DATABASE Dbtesting SET MULTI_USER
What else you can do when DBCC CHECKDB fails?
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.
Use Stellar Repair for MS SQL software to repair severely corrupt MS SQL database and restore all its components, while maintaining database integrity. The SQL Recovery Software helps reinstate access to the database with minimal manual efforts and time.
- Repairs both MDF and NDF database files
- Recovers all database components including tables, keys, indexes, stored procedures, etc.
- Allows recovery of deleted records
- Recovers SQL tables with PAGE and ROW compression
- Supports selective recovery of database objects
- Previews recoverable database objects
- Available for Windows and Linux OS
To know the complete working process of the software, watch the video
DBCC CHECKDB can help check consistency errors and corruption in MS SQL database. 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.