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:
RESTORE VERIFYONLY FROM DISK = 'backup_with_checksum.bak’
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:
RESTORE DATABASE AdventureWorks2022
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2022.bak' ;
RESTORE DATABASE stellardb
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
If the above methods fail to repair the damaged or corrupted MDF file, then the best solution is to use a trustworthy SQL database MDF file repair software, like Stellar Repair for MS SQL. It is an advanced software to repair corrupt SQL database (MDF/NDF) files without any data loss. It can even fix severe corruption issues within the MDF and NDF files. It can recover all the objects from the corrupt MDF/NDF file with complete integrity. The software supports SQL 2022, 2019, 2017, 2016, and earlier versions.
Here are some remarkable features of this software:
- Repairs corrupt SQL database (both MDF and NDF) files
- Recovers tables, triggers, indexes, keys, rules, and other objects
- Repairs corrupt partition tables in the SQL database
- Recovers deleted records from corrupted SQL database tables
- Compatible with Windows and Linux operating systems
- Allows searching and recovering specific database objects
- Facilitates saving repaired file in MS SQL (MDF), HTML, XLS, and CSV formats
- Supports ROW and PAGE compressed data recovery
- Recovers all the data in original format
- Interactive and easy to follow user interface
Follow the steps below to repair the MDF file using the software:
- Download, install, and launch the Stellar Repair for MS SQL software.
- In the Select Database window, click Browse to select the MDF file (You can use the Find option if you do not know where your data file is located).
- 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.
4 min read
