The dynamic and complex Master Database File (MDF) acts as a core component of Microsoft SQL Server. This primary database file stores database schema including triggers, stored procedures, queries, and sensitive data tables.
SQL Server also stores data in the Log Database File (LDF) and Secondary Database File (NDF) but MDF remains at the core of the database structure. In the event of any corruption in this file, it could corrupt the entire database. It is therefore imperative to maintain the integrity of the MDF file.
MDF File Repair in SQL Server Database
To proceed with MDF file repair, you can adopt several methods as a database admin. For instance, restoring the database from a recent backup is the foremost option. If this does not work, you may try using the DBCC CHECKDB Command to repair the MDF file.
If none of these manual ways work, you can Repair the MDF file in SQL Server Database with the help of a third-party SQL repair tool. Stellar Repair for MS SQL is a reputed software to resolve this issue.
The foremost step to repair a damaged MDF file should be to use a recently created backup to restore the database.
Before you do the restore operation, make sure to verify the integrity of the backup file. You can use the RESTORE VERIFY ONLY command for this purpose. Here is the script.
RESTORE VERIFYONLY FROM DISK = ‘backup_with_checksum.bak’
This command will verify the backup file for the following information:
- Readability
- Valid format
- Complete backup
- Presence of CHECKSUM
After the verification is complete, run the command as shown below to restore the backup file:
RESTORE DATABASE [SQL_TEST_DB]
FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL16.SQLEXPRESS\MSSQL\Backup\SQL_TEST_DB.bak’
WITH FILE = 1, NOUNLOAD, STATS = 5
Please note that in this case, the database is SQL_TEST_DB and the backup file name is SQL_TEST_DB.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.
Repair MDF File using the DBCC CHECKDB Command
Admins use the DBCC CHECKDB command to check the physical and logical integrity of a database and its objects. It can also repair issues arising in the MDF file. Here are the steps to repair a corrupt MDF file with the help of this SQL Server command.
Step 1: Use the DBCC CHECKDB command
Open SQL Server Management Studio (SSMS) and connect to your database engine. Navigate to a new query window and execute the following SQL command: DBCC CHECKDB (‘YourDatabaseName’)
Example: DBCC CHECKDB (‘SQL_DB’)
Analyze the output in the message box below the query window to find out if the query has identified any errors. This will help you to understand the nature of corruption in the MDF file.
Before using this command, make sure to log in with your administrator credentials. The result will suggest an appropriate repair level depending on the types of database corruption.
Step 2: Set the database to emergency mode
In case the file is inaccessible, switch the status of the database to Emergency mode, allowing read-only permission to the administrator.
ALTER DATABASE SQL_DB SET EMERGENCY;
Run the DBCC CHECKDB command again. If it detects corruption in the database, proceed with the repair operations recommended by the command.
Step 3: Reduce the effect of the repair operation
Your repair work should not interfere with any other database operations running in parallel. To ensure this, turn the database to single_user mode.
ALTER DATABASE SQL_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Step 4: Execute the repair options
Use the following options in combination with the DBCC CHECKDB command to repair the MDF file in the SQL Server database.
REPAIR_FAST
It can rebuild the corrupt MDF file quickly. It however does not carry out repair but only checks the file for logical consistency and looks after the backward compatibility syntax. Here is the command.
DBCC CHECKDB (SQL_DB, REPAIR_FAST)
REPAIR_REBUILD
It can rebuild corrupt file pages for minor data loss or repair the missing rows in the indexes. Nevertheless, it does not repair the database that consists of FILESTREAM data. The command is as follows:
DBCC CHECKDB (SQL_DB, REPAIR_REBUILD)
REPAIR_ALLOW_DATA_LOSS
If none of the two options works to repair the file, you may try REPAIR_ALLOW_DATA_LOSS. Considering the fact that it can result in severe data loss, use this option only during an emergency. Use the command below to perform this repair action.
DBCC CHECKDB (SQL_DB, REPAIR_ALLOW_DATA_LOSS)
Also, ensure to have a backup of the corrupted database before trying this option
Step 5: Switch the database back to SET MULTI_USER mode
After doing the repair operations, restore the database accessibility for all users by using the command below:
ALTER DATABASE SQL_DB SET MULTI_USER;
Step 6: Verify and review the MDF file
Recheck the database to confirm its consistency and integrity after the repair.
DBCC CHECKDB (SQL_DB);
No error message will indicate the success of the repair operation. If you still encounter an error, it is high time to use a tool for automated SQL repair.
MDF File Repair Using Third-Party Tool
In case the manual repair using DBCC CHECKDB commands does not attain fruition, opting for an automated solution would be ideal. For instance, you can use Stellar Repair for MS SQL to repair the MDF file in the SQL Server Database. It will reduce downtime appreciably and reduce the need to opt for expensive database recovery services.
This software is also beneficial if you are looking for a comparably simpler, quick, and user-friendly solution to repair the corrupt MDF file. The good thing is that it is compatible with the existing and earlier versions of Windows OS.
Prominent Features
Here is a list of features of Stellar Repair for MS SQL
- Repairs corrupt MDF and NDF database files
- Records all the database objects including tables, keys, triggers, indexes, etc.
- Restores deleted data from the corrupt database tables
- Allows saving the repaired file in several formats – HTM, XLS, CSV, an MS SQL (MDF)
- Easy to understand and interactive UI
- Repairs the entire data in its original format
- Ensures complete integrity during MDF file repair
- Supports SQL Server 2022, 2019, and previous versions
Repair the MDF file using Stellar Repair for MS SQL
Here is quick look at the steps to repair the damaged or corrupt MDF file by using this popular SQL repair tool.
- Launch the MDF file repair software
- Click Browse to select the corrupt MDF file
- Use Find if you do not know the location of the MDF file
- Click Repair button
- Next, click OK.
- After successful repair, you can check the preview of the repaired MDF file
- Choose the objects you wish to save.
- Click Save Button to save the repaired file to a new database, live database, or HTML, Excel, or CSV formats
- Fill in the credentials and other details under Connect To Server, and click Next.
- Choose the suitable saving mode and click Save.
- Next, click Ok.
This will repair your MDF file in the least possible time with maximum precision. Using it for your office work will save considerable time. To start with, you can use the trial version, which is free. If you wish to benefit from its advanced capabilities, you can choose to buy its paid version.
Wrapping up
Your MDF file in the SQL Server Database may become corrupt due to several reasons. Dealing with the issue promptly is inevitable to avoid further loss of data and the company’s reputation. As a quick method, you can use a recently created database to repair the database.
If it does not work, try using the DBCC CHECKDB command with REPAIR_FAST, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS options. Before proceeding with any of these options, set your database in emergency mode and then in single-user mode.
This will ensure that the database is in single hand only, making it easier to carry on the repair work. After the successful completion of the repair, switch the database consisting of the repaired MDF file back to Multi-User Mode and run the DBCC CHECKDB command again. If the database is accessible, the repair operation has succeeded.
If the problem prevails, using an MDF file repair tool such as Stellar Repair for MS SQL would be ideal. It will help you to resolve the database corruption in the least time and with maximum accuracy. You can use the free trial version of this tool. If you find it useful, you may also buy its paid version.
For additional training resources, check out our online training courses.
