How to Repair MDF File in SQL Server Database?

MDF (Master Database File) is the primary database file in MS SQL Server. Each SQL Server database contains at least one MDF file. This primary data file contains the SQL database schema and all the data, including indexes, views, tables, triggers, stored procedures, and other critical components. Therefore, this file is essential for data storage in MS SQL Server environment.

However, sometimes, the SQL database MDF file gets corrupted or damaged due to various internal and external factors. When the MDF file gets corrupted, the entire SQL database becomes unavailable. In addition, you may experience various error messages when accessing the SQL database. In this article, we will see the causes for corruption in MDF file and discuss some effective ways to repair the MDF file in SQL Server and recover the data.

Possible Causes for Corruption in MDF File

Following are some common reasons that may lead to corruption in SQL database MDF file:

• Sudden power failure or server shutdown
• Bugs in the SQL Server
• Faulty operating system
• The hard drive where MDF file is stored has bad sectors
• Unexpected (abrupt) system shutdown or crash when the database file is open
• Hardware or software issues
• Virus or malware infection
• Lack of storage space on the hard drive where the file is stored

Effective Ways to Repair MDF File

Here are some effective ways to repair corrupted MDF file and recover the data.

Method 1 - Restore MDF File from Recent Backup

If your MDF file is corrupted or damaged, the first thing you can do is try to restore the file from backup, if available. Before restoring, check the integrity of the backup file using the RESTORE VERIFY ONLY command as given below:

This command helps you verify if the backup file is readable, its format is valid, the backup set is complete, and CHECKSUM is present in it.

Once verified, you can run the below command to restore the backup file:

Method 2 – Repair MDF File using the DBCC CHECKDB Command

If the backup file is not available or is obsolete, then you can use the Database Console Command (DBCC CHECKDB) to repair the damaged or corrupted MDF file.

First, run the DBCC CHECKDB command to check integrity issues and corruption errors in the database (MDF) file as given below:

If you fail to access the file, then change the database status to Emergency mode to provide read-only access to the administrator. To set the database to EMERGENCY mode, run the below command:

Then, again run the DBCC CHECKDB command. If the DBCC CHECKDB command detects corruption in the database, it will recommend an appropriate repair option. You can use the repair option to repair the corrupt MDF file in SQL Server.

Before repairing, you need to set the database to single user mode. You need to have the ALTER permissions on the database to set it to single user mode. Run the following command to set the database to single user mode:

After setting the database to single user mode, you can repair the database by using the following options:

• REPAIR_FAST
• REPAIR_REBUILD
• REPAIR_ALLOW_DATA_LOSS

Let’s see how to use these options.

If you want to rebuild the MDF file quickly, then use REPAIR_FAST. It only checks the logical consistency and maintains syntax for backward compatibility. It does not help perform any repair actions. The syntax for this repair option is:

The REPAIR_REBUILD command helps rebuild corrupt pages or repair missing rows in non-clustered indexes. However, this command does not repair database that includes FILESTREAM data. Here’s the command to repair the database using the REPAIR_REBUILD option:

If the above repair options fail to repair the database file, then you need to use the REPAIR_ALLOW_DATA_LOSS option to repair the database. However, this command may result in data loss. So, you should use this repair option as the last resort. Here’s the command to repair the database using the REPAIR_ALLOW_DATA_LOSS option:

Method 3 - Repair MDF File using a Third-Party SQL Database Repair Software

• Next, click on the Repair button.
• In the Repair Complete window, click OK.

• Once repaired, the software shows a preview of the repaired file.
• You can select the objects you want to save. Then, click the Save button. You can save the repaired data to a live database, in a new database, or other formats, like CSV, HTML, and Excel.

• Select the saving option (for example, New Database) and then click Next.
• Under Connect To Server, fill in the credentials, select the location, and click Next.
• In Save Mode window, select the appropriate saving mode and click Save.
• On the Save Complete window, click on OK.

Conclusion

In case of MDF file corruption, backup helps in restoring the data. But if the backup is not available, then you can use the DBCC CHECKDB command to repair the MDF file in the SQL Server. Above, we’ve discussed the stepwise instructions to use the DBCC CHECKDB command. If the DBCC CHECKDB command fails to provide the desired results, then use Stellar Repair for MS SQL software. It is a professional repair software to repair corrupt SQL database (both MDF and NDF) files without any data loss.