How to Repair a Corrupted Database in SQL Server

Of course, it’s frustrating to discover a corrupted database on your SQL Server for apparent reasons. You can’t access the information, and your work stops.

However, you can fix this problem in many ways with manual solutions or by using a smart, dedicated third-party tool called Recovery Toolbox for SQL Server.

But before starting the troubleshooting, let’s understand and investigate why this problem is happening in the first place.

What is an SQL Server, and how can we identify the database?

An SQL Server is a database system developed by Microsoft that is used for managing, storing, and retrieving data by other applications.

There are three types of files to store and manage data in an SQL Server: primary files (with a .MDF extension), secondary files (with the .NDF extension), and log files (with the .LDF extension).

We are particularly interested in those MDF files because those are the ones that provide the database storage.

Why does a database become corrupted on an SQL Server?

There are a lot of possible reasons why your SQL server database can become corrupted, from a simple overwrite or accidental file deletion to virus infections and incorrect configuration.

Let’s go through some of the most common causes for this issue:

• Overwriting a file – If you write over an existing MDF file by mistake, you risk corrupting the database
• Trying to unlock the SQL database – If you forgot the SQL password and enter it wrong too many times, this can lead to SQL Server Corruption
• Failure on your storage drive – It goes without saying that a drive malfunction can produce file corruption, including the database
• Problems with SQL database configuration – If the SQL configuration changes, that can disable the access on MDF files
• Virus infection – This is a common problem for any file corruption, not only the SQL database files and even if you remove the virus by using the best antiviruses, unfortunately, you might still lose some data
• SQL internal bugs – Although it’s a strong solution, it may still have its bugs that can cause MDF files corruption

That said, follow our step-by-step guide to learn how to repair an SQL database file on an SQL Server.

How can I repair a corrupted database in SQL Server?

NOTE

Before starting anything, perform a backup of the SQL library.

1. Restart the SQL Server

1. Open a terminal and enter the following command to stop the SQL Server: systemctl mysql stop
2. To check the status of the SQL Server, type the following command and press Enter: status mysql.service
3. Now restart the SQL Server by performing the following command in the terminal: restart mysql.service

Sometimes, you can solve the problem just by restarting the SQL Server. Of course, it doesn’t work every time but it’s so easy to perform that it wouldn’t hurt to perform.

2. Repair a damaged table

1. Start the SQL Server.
2. Type the following command and press Enter to run it: CHECK TABLE name_of_the_table;
3. If it detects any problems with the table, type the following command and press Enter: REPAIR TABLE name_of_the_table;

The REPAIR TABLE will help you repair a table and you can do the same for any other corrupted ones but this way, you might lose some data from the table.

3. Use the mysqlcheck command

1. Open a terminal and type or paste the following command to reach the database folder and press Enter: cd /var/lib/mysql
2. Now, type the following command to test a table and press Enter. Make sure you replace <database> and <table name> with the actual names: mysqlcheck <database> <table name>
3. If you get the message that the table is corrupted, type the following command to repair it and press Enter: mysqlcheck -r <database> <table name>

4. Use the ALTER TABLE commands

1. Type the following command to check your table type (in our case, it’s an MyISAM table): SHOW CREATE TABLE <table name>;
2. To rebuild the table, type the following command and press Enter: ALTER TABLE <table name> ENGINE = MyISAM;
3. If you have a InnoDB database, type the following command instead: ALTER TABLE <table name> ENGINE = InnoDB;

Read more about this topic

• Download and install Microsoft SQL Server 2019
• Best SQL server backup software [Free & Paid]
• How to secure an SQL server database

5. Repair the database using Recovery Toolbox for SQL Server

1. Go to the Recovery Toolbox for SQL Server dedicated page and click the Download button.
2. Install the app after choosing the language and accepting the license agreement.
3. Click Finish to launch the SQL Server MDF repair tool.
4. Select the corrupted *.mdf file by clicking the source folder icon.
5. Once you’ve selected it, click the Next button.
6. You will now be able to preview the data that can be retrieved from the corrupted Microsoft SQL Server database. Click Next on the Microsoft SQL MDF recovery tool window to start the process.
7. Now, you only need to choose the method of the data export. You can save it as SQL scripts to disk and even execute the SQL script directly on the database.
8. Finally, choose what data to save and click on Start Recovery.

So, this is how to repair SQL server database files with this simple and effective tool. As you can see, it will get the job done much faster.

The MS SQL server recovery tool will also help you if you’re facing the following problems with the MDF database:

• Microsoft SQL Server error 5172 – The FILE SIZE property is incorrect
• SQL Server Detected A Logical Consistency-Based I/O error – It’s an incorrect Checksum, otherwise known as error 824 in the SQL Server
• The Index Allocation Map (IAM) page is pointed to by the Next Pointer of an IAM Page – It’s a pretty common problem
• I/O error, bad page ID, detected when reading at offset 0x###### in file FileName.mdf – This doesn’t appear too often, but you can still repair it with this small tool
• File appears to have been truncated by the Operating System error – Most likely caused by a configuration problem
• An Error Occurred At Log Record ID – It occurs during the redoing of a logged operation in the database

Actually, the Recovery Toolbox for SQL Server repairs all objects of corrupted .mdf files, including data types, table cell data, views, stored procedures, custom functions, triggers, indexes, primary and foreign keys, restrictions, and others.

And the great advantage is that you can seamlessly export the repaired data into the Microsoft SQL Server from the app.

This is it! Now you know how to repair your SQL Server database manually, or by using a dedicated tool, you can export it to Excel and open it to analyze the data.

To learn more about SQL issues, we have a great guide on how to fix Arithmetic overflow error converting expression to data type int error, so don’t miss it.

If you know additional methods or have any questions, we await them in the comments section below.