Some SQL users have complained about encountering errors and issues while accessing MDF file in different forums. The error includes “MDF File not Opening in SQL Server, SQL Server error 5171: MDF is not a primary database file and more. Such errors can occur in any version of SQL Server and usually take place when the Server fails to recognize the MDF file or detect inconsistencies in the pages or other objects in the file. Such errors indicate corruption in the MDF file. File Corruption in SQL database can threaten database integrity. So you need to recover data from the corrupt database file as soon as possible to prevent data loss and ensure business continuity. Here in this article, we will discuss some efficient methods to recover corrupt MDF files of SQL Server 2022.
Causes of corruption in MDF file
Some common reasons that may result in MDF file corruption are as follows:
- Abnormal System Shutdown
- Storage Size Issue
- Hardware Issue or Faulty Memory
- Virus Infection on the system hosting MS SQL Server.
- Power outage while working on the database
How to Diagnose Corruption in MDF file in SQL Server?
You can run the DBCC CHECKDB command as given below to detect corruption errors in the database.
DBCC CHECKDB(‘Database87’)
The above DBCC CHECKDB command will detect corruption errors in the database named database87.
Additionally, you can see the ErrorLog in SQL Server for detecting corruption issue. For this, open the SQL Server Management Studio (SSMS) and go to Management > SQL Server Logs. You can refer the suspect page table to identify the corrupt pages in MDF file.
Methods to recover corrupt MDF file of SQL Server:
The first step would be to use a backup (.bak) file to restore corrupt MDF file. So, make sure you have a healthy backup. You can use RESTORE Statements-VERIFYONLY to check whether the backup is readable or not. If your backup file is readable, then use the below steps to restore BAK file in SQL Server:
- Open SQL Server Management Studio (SSMS), and then connect to SQL Server instance.
- Expand the Databases folder in Object Explorer.
- Right-click on the option labeled Database, and then click Restore Database.
- In Restore Database window, below Source for restore section, select From device, and then click the option next to it to specify database file location.
- In Specify backup window, select Backup media type, and then click Add button to insert the backup file location.
- Select the backup(.bak) file you want to restore and then click OK.
Alternatively, you can use the Transact-SQL command to restore backup files in SQL Server 2022.
Method 2- DBCC CHECKDB Command to repair MDF file
If the bak file is not readable, then you can repair the corrupted MDF file using the DBCC CHECKDB command. This command allows you to use different repair options, depending on the severity of the corruption in the file.
Let’s understand how to run the different repair options in DBCC CHECKDB
REPAIR_REBUILD
You can use the DBCC CHECK command Repair option- REPAIR_BUILD to resolve minor issues in the MDF file. Here’s the syntax to run the command:
sql
DBCC CHECKDB(‘YourDatabaseName’, REPAIR_REBUILD);
GO
REPAIR_FAST
The DBCC CHECKDB command with the repair option-REPAIR_FAST is used to repair the SQL database file quickly. This option only maintains backup compatibility syntax. Also, it may not able to resolve complex corruption issues in the MDF file. Here’s the command:
sql
DBCC CHECKDB(‘YourDatabaseName’, REPAIR_FAST);
GO
REPAIR_ALLOW_DATA_LOSS
You can use the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS repair option to resolve the severely corrupted MDF file or complex database issues. Here’s how
sql
DBCC CHECKDB(‘YourDatabaseName’, REPAIR_ALLOW_DATA_LOSS);
GO
This repair option can resolve the errors in MDF file that are occurred due to corruption but does not guarantee the complete data recovery. In several case, it may deallocate the objects, like pages & rows in the table, leads to data loss.
What if nothing works?
If you do not have a backup and the DBCC CHECKDB fails to repair and recover the corrupt mdf file, then the only option is to use a third-party SQL database repair software, such as Stellar Repair for MS SQL. This software can easily repair the corrupt MDF files and recover all their data. It can also repair NDF data files. You can even restore selected objects from the corrupt MDF file. It allows you to preview all the recoverable data from the corrupt MDF file. You can store the recovered data from the corrupt MDF file in a new database or a live database in different formats like Excel, CSV, or HTML files. The tool supports recovery of the MDF files created in SQL Server 2022 and earlier versions.
Conclusion
You may fail to access the data from the corrupt MDF file in SQL server, which can impact your workflow. You can try the above methods to repair and recover all data from the corrupt MDF file in SQL Server 2022. If you have a complete, valid and readable backup, then you can easily restore corrupt MDF files. But if you have not created a bak file or your backup is not readable, then you can use SQL recovery software like Stellar Repair for MS SQL tool to repair and recover the corrupt or damaged MDF file. It can help you recover indexes, metadata, foreign keys, unique keys, and even deleted items from the MDF file with complete precision. You can download the tool’s demo version to check the functionality and accuracy before purchasing.
