SQL Server stores all the data, such as tables, indexes, views, triggers, etc. in database files (.MDF and .NDF). Like other database files, these files can get corrupted due to several reasons, such as virus attack on the system, hardware failure, software issues, etc. If there is corruption in these files, then you may encounter different errors or even fail to access the data. Therefore, it is crucial to quickly repair the database files to avoid any further harm or permanent data loss.
In this article, we will learn how to use DBCC CHECKDB command to repairs corrupt database and an advanced SQL database repair tool.
How to Repair SQL Database using DBCC CHECKDB Command?
DBCC CHECKDB is a database console command used to identify the status of the SQL Server database, check the errors, and repair the database. It checks the physical and logical integrity of all objects in the SQL database, index pages, system tables, and other allocation structures. If any of these checks fail, it reports consistency errors.
You can use the DBCC CHECKDB command to repair corrupt SQL database. Here’s how:
Note: To use DBCC CHECKDB, you need to install SQL Server Management Studio (SSMS) and have Administrator privileges to use the command.
First, run the DBCC CHECKDB command to check corruption errors in the database. If you fail to access the database, then change the database status to Emergency mode to provide read-only access to the administrator.
To set the database in EMERGENCY mode, run the below command:
Once the administrator can access the database, then run the DBCC CHECKDB command as given below:
If DBCC CHECKDB detects corruption in the database, it will recommend an appropriate repair option.
To use the repair options, you need to set the SQL Server database to SINGLE_USER mode. Make sure you have the ALTER permission on the database. Here’s the command to set the database to SINGLE USER mode:
If you want to rebuild the database without any data loss, then use REPAIR_REBUILD option. Here’s the command:
DBCC CHECKDB ('h', REPAIR_REBUILD)
GO
If you want to repair the SQL database quickly, you can use the below command:
DBCC CHECKDB ('h', REPAIR_FAST)
GO
Note: This repair option only maintains backward compatibility syntax and only checks the logical consistency.
If the above repair options fail to repair the database, you can run the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option as given below:
DBCC CHECKDB ('h', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
Note: The REPAIR_ALLOW_DATA_LOSS repair option can help you repair corrupt SQL database but it may not work in all scenarios. Also, it can cause data loss.
How to Repair SQL Database using a Third-Party SQL Database Repair Software?
If the above repair options fail or you don’t want to risk data loss, then you can use a professional SQL database repair software, like Stellar Repair for MS SQL. This MVPs trusted software can repair MDF/NDF files without any data loss. It can recover all the objects from the corrupted database file with complete integrity.
Here are the steps to repair corrupt SQL database using this software:
- Download, install, and launch Stellar Repair for MS SQL software.
- From the Select Database window, click Browse to select the corrupt database file you want to repair.
- On the Select appropriate scan mode window, you will see Standard Scan and Advanced Scan options to scan the corrupt database. Select the appropriate option and click OK. It will start the repair process.
- After repair, the software will display all the recoverable database components.
- Click the Save option on the File menu to save the repaired file.
- Note: To save the repaired file, make sure your system has MS Server installed and you’ve activated the Stellar Repair for MS SQL software.
- From the Save Database window, select MDF file, New Database, or Live Database, and enter the required server credentials.
- Click Browse to select the destination to save the repaired file.
- Click Save.
- In the Save Complete message box, click OK.
- The repaired file will be saved at the selected location.
Conclusion
In this guide, we’ve discussed how to use DBCC CHECKDB command to repair corrupt SQL database. If it doesn’t work, then you can use a professional SQL database repair tool, like Stellar Repair for SQL. It is a reliable and efficient tool for repairing MDF/NDF files. It can recover all the data from the corrupt SQL database. The tool supports all the SQL Server versions, such as 2022, 2019, 2017, 2016, and lower. You can download the tool’s free demo version to evaluate its functionality.