How To Repair Corrupt MDF File And Restore Database?

MDF files are the primary files in MS SQL which contain the database’s startup information. Each SQL database has one primary data file which has a .mdf extension. Like other files, MDF files are also prone to errors and inconsistencies issues. When such files get corrupted, it makes your database inaccessible and gives random errors. In this article, we will present the methods to repair corrupt MDF files and restore the database.

Causes Of Corruption In MDF File:

The MDF files can get corrupted due to various internal and external factors. Some of the reasons are cited below:

• Bugs in SQL Server.
• Server shutdown due to sudden power failure.
• Faulty Operating system.
• Hardware or software issues.
• Virus infection.
• Hard disk space is out of space.
• Hard-drive issues

Methods To Repair Corrupt MDF Files:

Below mentioned are methods to repair corrupt MDF files in MS SQL:

Method 1 – Restore Your Backup File:

If your MDF file or SQL database becomes corrupted, you can restore the backup file. Before this, you can check the integrity of the backup file. In SQL, you can use the RESTORE VERIFY ONLY command to check whether your backup file is readable or not. Here is the statement you need to check the backup (.bak) file:

RESTORE VERIFYONLY FROM DISK = 'backup_with_checksum.bak’

Once you have confirmed that your backup file is ready to use, then follow the below step-wise instructions to restore the corrupt MDF file from the backup:

• • Open SSMS, in Object Explorer, connect to an instance of the SQL Server Database engine and expand it.
  • Expand Databases, right-click on Database and then click Restore >Database…

• • On the General window, specify the source and destination location of the backup sets to restore.

• In the Restore to page, select the desired option.
• In the Backup sets to restore grid window, click on the backup file you want to store. This will display the available backup file for the specified location.
• Click

Method 2 – Use DBCC CHECKDB Command:

You can run the DBCC CHECKDB command to recover the data from the MDF file and SQL database. The DBCC CHECKDB command has different repair options like REPAIR_REBUILD, REPAIR_FAST, and REPAIR_ALLOW_DATA_LOSS. Follow the below command to use these repair options to repair the MDF file:

Note: In my case, the corrupted MDF file is followedemp.mdf.

• First, set the corrupted database to SINGLE USER mode:

ALTER DATABASE followedemp.mdf SET SINGLE_USER

• Next, use the REPAIR_BUILD option to repair the MDF file at a minimum repair level.

DBCC CHECKDB (' followedemp.mdf ', REPAIR_REBUILD)
GO

It can help you repair MDF files with minor corruption. But, it is a time-consuming option.

• You can even use the REPAIR_FAST option to perform repair tasks in SQL. Here is the command:

DBCC CHECKDB (' followedemp.mdf ', REPAIR_FAST)
GO

• If these repair options do not help you resolve errors in the MDF file, you can use the REPAIR_ALLOW_DATA_LOSS repair option of the DBCC CHECKDB command. However, this option can risk your data in the MDF file because it reallocates pages in the database.

DBCC CHECKDB (N ’ followedemp.mdf’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

Once the MDF file is repaired then you can access it to perform various tasks on MS SQL.

Method 3 – Use A Professional MS SQL Repair Tool:

If the above repair methods fail to repair your MDF file and MS SQL database, then you can take the help of an advanced MS SQL repair tool like Stellar Repair for MS SQL. The tool is built with advanced algorithms that can help you repair all objects of the MDF file without any data loss. It maintains integrity and precision while repairing database files. The tool can restore the deleted objects, including partition tables, from the damaged database and help you preview the repairable objects. It is compatible with Windows & Linux operating systems.

Conclusion:

When an MS SQL database or primary (MDF) file gets corrupted, you can perform a backup restore if you have a readable backup file. If your backup is not available, then you can use SQL’s built-in command, DBCC CHECKDB. Nevertheless, it results in data loss and takes a lot of time. You can use MS SQL database recovery tools like Stellar Repair for MS SQL to stop data loss. It is the best repair tool for recovering data safely. It can repair all files, including primary and secondary files, in the MS SQL database. It can even repair partition tables and other objects from corrupt databases with complete integrity. In addition, it supports ROW and PAGE compressed data recovery.