How To Repair A Corrupted Database In Sql Server

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 andwith the actual names: mysqlcheck
  If you get the message that the table is corrupted, type the following command to repair it and press Enter: mysqlcheck -r

  4. Use the ALTER TABLE commands

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

  5. Repair the database using Recovery Toolbox for SQL Server

  • Go to the Recovery Toolbox for SQL Server dedicated page and click the Download button.
  • Install the app after choosing the language and accepting the license agreement.
  • Click Finish to launch the SQL Server MDF repair tool.
  • Select the corrupted *.mdf file by clicking the source folder icon.
  • Once you’ve selected it, click the Next button.
  • 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.
  • 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.
  • 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 a lot faster.

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

  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 to 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, with Recovery Toolbox for SQL Server repair 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 export the repaired data directly into the Microsoft SQL Server seamlessly from the app.

  This is it! Now you know how to repair your SQL Server database manually or by using a dedicated tool.

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

  • sql server

  Email *

  Commenting as . Not you?

  Comment