SQL Server uses an MDF file, also referred to as the primary database file, to store all the data and database objects like tables, stored procedures, indexes, etc. Corruption in the MDF file can render a database inaccessible and may return errors 823, 824, 825, or others.

The best solution is to restore a database from the last valid backup. But, if the backup is corrupt or obsolete, or you don’t have one, you will need to repair the SQL database.

This article discusses the possible reasons behind MDF file corruption and methods to repair and restore the database objects.

What Causes MDF File Corruption

The primary database file (MDF) may become corrupted due to any of these reasons:

  • An issue with the disk I/O subsystem is the most common reason behind a corrupt MDF file.
  • A ransomware attack may leave the database files damaged and inaccessible.
  • A system shuts down abruptly before cleanly closing the database.
  • SQL Server data page in the memory is corrupt

Before implementing the methods to repair and restore the database, check if hardware or driver issues have led to the corruption of the primary database file. If so, address the problems with your hardware and then continue with the following methods.

Methods to Repair MDF File and Restore SQL Database Objects

Here are the methods you can use to fix a corrupted MDF file and restore the SQL database objects:

Method 1 – Run DBCC CHECKDB with Repair Options

Check for corruption in the database by running the following DBCC CHECKDB command. Use WITH NO_INFOMSGS in the DBCC CHECKDB command to avoid displaying informational messages and show only errors found in a database:

DBCC CHECKDB (Stellar_TestDB) WITH NO_INFOMSGS

Here is the output of the above command:

As you can see, CHECKDB found three consistency errors in the database and recommends running ‘repair_allow_data_loss’ as the minimum repair level to correct the errors.

Note: Running the repair_allow_data_loss command may result in data loss. If you need to rebuild an index or repair missing rows of non clustered indexes, run the CHECKDB command with the REPAIR_REBUILD option.

After running the repair option, run DBCC CHECKDB again on the database. If it doesn’t find any consistency errors, the database is fixed.

Read this: What to do When DBCC CHECKDB Will Not Work?

Method 2 – Use a SQL Database Repair Tool

If none of the above methods repairs the MDF file, use a SQL database repair tool such as Stellar Repair for MS SQL to resolve the problem. The software can repair severely corrupt SQL database files (MDF and NDF) and perform a fast database restore process.

It helps retrieve all the database objects such as tables, triggers, indexes, views, stored procedures, etc., from the corrupted file.

Once repaired, the tool allows saving the file in a New Database, Live (i.e., existing) Database, or Other File Formats (CSV, HTML, or XLS). You can preview all the repairable objects before saving them using the demo version of the software.

End Note

The primary data file of a SQL database (i.e., MDF file) can get corrupt due to hardware issues, ransomware attacks, an unclean shutdown of a database, etc. Corruption in the MDF file makes a database inaccessible.

First, try restoring the file from the most recent backup to get the database online.

If the backup is not an option, run DBCC CHECKDB with a repair option to correct the corruption errors. If nothing works, use a SQL database repair tool to fix the corrupted MDF file and restore the database and its objects to the original state.

But remember, what happens once can happen again. Despite taking preventive measures, SQL database files may get damaged. So, it is crucial to define the right backup and recovery plan to safeguard and protect your data.

To create a backup and recovery plan, keep these pointers in mind:

  • The type of data you want to restore.
  • How frequently run should back up the database be.
  • Decide the backup media where you want to save the database copy. It could be a tape drive, network share, or cloud.
  • A backup is of no use if you fail to restore the database. So, ensure to test your backups regularly.