October 6, 2024

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.

Repair Corrupt SQL Database

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:

 ALTER DATABASE [h] SET EMERGENCY

Once the administrator can access the database, then run the DBCC CHECKDB command as given below:

 DBCC CHECKDB (h)

Run the DBCC CHECKDB command in SSMS

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:

 ALTER DATABASE test SET SINGLE_USER

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.
Select standard scan or advanced scan options to scan the corrupt database

  • After repair, the software will display all the recoverable database components.
recoverable database components in Software

  • 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.
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.
select the destination to save the repaired file

  • Click Save.
  • In the Save Complete message box, click OK.
Database repaired successfully window

  • 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.

By admin

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *