A Master Data File or MDF is the primary database in SQL Server, which contains startup details of a database. In addition, the user-defined secondary data files (.ndf) and transaction log files (.ldf) are the other file types contained in a database. Considering the fact that an MDF file is the fundamental component needed to store and access the information in a database, it is of utmost significance.
Any kind of damage to this file can compel an organization to bear huge losses in terms of time, effort, money, and reputation. Therefore, prompt repair of the damaged Microsoft SQL Server MDF file is crucial. In this blog, we will discuss several ways to repair an MDF file. Moreover, we will also talk about the role of professional SQL recovery software, such as Stellar Repair for MS SQL, in repairing the damaged MDF file.
Reasons behind a corrupt MDF file
Several logical and technical reasons can be the reason behind a damaged MDF file. Here is an insight into these factors:
Logical Reasons
- Flawed server upgrade
- Leveraging the database with a compressed file
- Damaged header file
- Corrupted file storage medium
- Network error while the SQL Server is running
- Abrupt system shutdown, power failures
- Hard drive failure due to Virus or malware attacks
Technical Reasons
- Lack of space in the drive to store MDF and NDF files together
- An attempt to copy data in a running SQL Server
- Performing almost any operation in a running SQL Server
- An already corrupted LDF or Log file
- Modifying the Service Account password without updating it in the SQL Server
- Startup parameters have the wrong path locations
Manual ways to repair a damaged Microsoft SQL Server MDF file
Several ways can repair the corrupt MDF file. The foremost of them is to use the DBCC CHECKDB command. Another method is to use the REBUILD option to detach and reattach the database. Before trying any of these methods, experts recommend having a healthy and recent backup of the database. Once you have it, you may proceed with the restore. To initiate the repair, start SQL Server Management Studio (SSMS) and open the New Query window.
Repair using the DBCC CHECKDB Command
Expert admins use this command to repair the corrupt MDF files. We have used TestDB as the database name. Replace it with the name of your specific database. Here are the necessary steps:
- Set the database having the corrupted file to single-user mode by using the command below in the SSMS New Query window:
ALTER DATABASE [TestDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
- Next, run the DBC CHECKDB command on your database
DBCC CHECKDB (‘TestDB’)
- If the command does not return any error, you don’t need to take any further action.
- If the command detects an error, you can try the REPAIR_REBUILD option with DBCC CHECKDB as given below:
DBCC CHECKDB (‘TestDB’, REPAIR_REBUILD);
This solution can troubleshoot the error without causing data loss. But, it is too slow and does not completely guarantee the safe repair of the MDF file.
- You may use the REPAIR_FAST command to repair the corrupted MDF file quickly. Here is the command:
DBCC CHECKDB (‘TestDB’, REPAIR_FAST);
However, it can fix only low-level corruptions. For major issues, you have to rely on the REPAIR_ALLOW_DATA option.
- Use the REPAIR_ALLOW_DATA_LOSS option through the command below:
DBCC CHECKDB (‘TestDB’, REPAIR_ALLOW_DATA_LOSS);
- After running this command, run the DBCC CHECKDB command again to ensure the resolution of MDF file corruption by using the command below:
DBCC CHECKDB (‘TestDB’)
- Most probably, it will resolve the error, but during the process, it will erase the entire damaged portion of the file instead of fixing it. Therefore, use this option only if the other options are unable to fix the issue.
- Set the database back to multi-user mode by using the command below:
ALTER DATABASE [TestDB] SET MULTI_USER;
Detach and Attach the SQL Server Database
If SQL Server is unable to attach a damaged MDF file directly, a suitable alternative is to detach the database and then reattach it by using the REBUILD option. Let’s name our database as TestDB
- Follow the command below to detach the database
EXEC master.dbo.sp_detach_db @dbname = N’TestDB’
- Move the MDF and LDF files to a different folder on your system. For example, C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup
- Next, reattach the database by using the requisite commands as shown below:
CREATE DATABASE [TestDB] ON
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\TestDB.mdf’ ),
( FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\DATA\TestDB_log.ldf’ )
FOR ATTACH
Alternatively, you can also use SQL Server Management Studio GUI to serve this task. Replace TestDB and the file path with the actual name and location on your system.
Using professional SQL recovery software to repair the damaged MDF file
The methods to repair corrupt or damaged MDF files can work successfully, but they need considerable time and effort. Some methods, like REPAIR_ALLOW_DATA_LOSS, can also delete important data, which can cause severe issues in the functioning of your organization. Using professional SQL recovery software such as Stellar Repair for MS SQL avoids such hassles and repairs the corrupt files without hassles.
Prominent features of Stellar Repair for MS SQL
- Repairs Corrupt MDF and NDF Files and restores the database to its original state
- Retrieves deleted records without modifying the table schema
- Allows saving the repaired file to a new database or live database
- Allows saving the repaired file to other formats, such as CSV, HTML, and XLS
- Offers Standard and Advanced Scan for scanning and repairing corrupt SQL database files
- The free version shows a preview of the repairable objects in the SQL Server database
- The free edition also lets you select specific objects you want to restore
- Saves the repaired file by connecting to SQL Server with Windows authentication or the SQL Server authentication method
- Allows saving log reports to analyze the repair process at a later stage
- Repairs the corrupt .bak file and restores the SQL Server database,
Repairing the damaged MDF files with Stellar Repair for MS SQL requires you to follow a few simple steps. For details, you can download the user manual. Moving over, when buying the professional SQL recovery software, you can choose from Corporate, Technician, and Toolkit versions. All come loaded with features and help to secure the database for the smooth working of your organization.
Conclusion
The SQL database in your organization can become corrupt due to power failure, malware intrusion, hard disk crash, and many more logical or technical reasons. In any case, a corrupt MDF or NDF file can create severe losses to your company, which necessitates the need for prompt resolution. Experts use several manual ways to resolve the issue, including the use of DBCC CHECKDB commands or detaching and reattaching the corrupt database.
These methods can work successfully, but take too much time and do not guarantee 100% success in the database repair. Using professional SQL recovery software can rescue you from this situation. MVP-recommended tools, such as Stellar Repair for MS SQL, work actively in repairing the damaged Microsoft SQL Server MDF file in this regard. The software is easily available from the official website of Stellar Info. You may also opt for its free trial version to start with.
