Of course, it’s frustrating to discover a corrupted database on your SQL Server for obvious reasons. You can’t access the information and your work hits a full stop.
- Data loss is a common problem, but we will show you how to repair a corrupted database in SQL Server.This issue can originate from a simple overwrite or accidental file deletion to virus infections and incorrect configuration.The storage drive malfunctioning can also produce file corruption and that includes your SQL database.
However, there are a lot of ways you can fix this problem with manual solutions or by using a smart, dedicated tool third-party tool called Recovery Toolbox for SQL Server.
But before starting the troubleshooting, let’s understand and investigate a little bit why is this problem 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 being 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?
1. Restart the SQL Server
- Open a terminal and enter the following command to stop the SQL Server: systemctl mysql stop
- To check the status of the SQL Server, type the following command and press Enter: status mysql.service
- 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
- Start the SQL Server.
- Type the following command and press Enter to run it: CHECK TABLE name_of_the_table;
- 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
- Open a terminal and type or paste the following command to reach the database folder and press Enter: cd /var/lib/mysql
- Now, type the following command to test a table and press Enter. Make sure you replace
and