MDF or Master Data File, also known as the primary data file, is used to store all the information (user data and objects) for a SQL Server database. A database may also use a secondary data file (NDF), an optional file used to store user data that can be spread across multiple disks.
Also, SQL Server databases have a log file (LDF) that contains a log of recent transactions (INSERT, UPDATE, and DELETE) executed by the database. The log file can perform “point-in-time” restores in the event of hardware failure or unexpected system shutdown. You can use a log file to rebuild a corrupted database, but this will only work if you have a backup to restore from. If you don’t have database backup, you cannot use an LDF file to recover the database. In that case, you will need an MDF file to restore the database to its original state.
However, at times, MDF files can turn corrupt due to factors like I/O subsystem failure, SQL Server crash, ransomware attack, etc.
In this post, we will be discussing different methods to repair SQL database MDF files without backup.
Methods to Repair MDF Files in SQL Server 2019
You can try to repair an MDF file manually or by using an MDF file repair tool. Let’s discuss both these methods in detail.
Method 1 – Repair MDF File in SQL Server Manually
If you don’t have log file and backup to restore the database, you can try to repair MDF file using DBCC CHECKDB, and here’s how:
Step 1: Put SQL Database in Emergency Mode
You will have problem accessing your database when MDF file gets corrupted. In that case, set the database status to EMERGENCY mode. Doing so will provide read-only access to the database, but you will be able to run DBCC CHECKDB for the database.
To set the inaccessible database to EMERGENCY mode, run this query in SQL Server Management Studio (SSMS):
|ALTER DATABASE [Stellar_TestDB] SET EMERGENCY|
Step 2: Run DBCC CHECKDB with NO_INFOMSGS
Once you’re able to access the SQL database, execute the DBCC CHECKDB command with NO_INFOMSGS parameter. That’s because running the command can produce hundreds of thousands of lines of code. Searching for the error message suggesting corruption among these lines of codes can take time. However, using DBCC CHECKDB with NO_INFOMSGS parameter avoids producing irrelevant output and will only show you the errors.
|DBCC CHECKDB (Stellar_TestDB) with NO_INFOMSGS; GO|
Executing the above query will produce the following output:
As you can see, running DBCC CHECKDB for ‘Stellar_TestDB’ database found consistency errors and suggests ‘repair_allow_data_loss’ as the minimum repair option.
Caution! Using DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS can lead to data loss. This is why Microsoft recommends using the repair option as a last resort to handle database corruption. But, you can repair MDF files without losing any data by using a specialized MDF file repair tool.
Step 3: Put the Database in Single_User Mode and Repair it
To run DBCC CHECKDB with the minimum repair option, i.e., REPAIR_ALLOW_DATA_LOSS, you’ll need to set the database status to single_user mode. For this, run the following query:
|ALTER DATABASE Stellar_TestDB SET SINGLE_USER DBCC CHECKDB (N ’Stellar_TestDB’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; GO|
Hopefully, executing the above query in SSMS will fix database corruption and restore the MDF file. If this doesn’t fix MDF file corruption or has caused data inconsistency errors, try the next method.
Method 2 – Use MDF File Repair Tool
If running DBCC CHECKDB with the recommended repair option fails to fix a corrupted database file (MDF), consider using an MDF file repair tool such as Stellar Repair for MS SQL. This tool is purpose-built for fixing corrupted primary data files. Also, it helps repair NDF files and recovers all the database objects without any data inconsistency errors.
Key features of the tool:
- Supports repairing MDF files in SQL Server 2019, 2017, 2016, and earlier versions.
- Repairs SQL data files on Windows as well as Linux systems.
- Restore access to deleted table records.
- Handles all SQL corruption errors like 824, 825, DBCC CHECKDB reported consistency errors, etc.
- Previews repairable data, including tables, stored procedures, triggers, etc.
Concluding Lines: Determine the Root Cause behind Corruption
Once you’ve repaired the MDF file, you must determine what caused corruption in an MDF file. Such information will help you take precautionary measures to prevent corruption from happening again.
Following are some methods using which you can find the cause of DBCC CHECKDB consistency errors:
- Check the Windows System Event Log to check for hardware-related errors.
- Check the SQL Error log to find more information about the error.
- Run the diagnostic tool provided by your hardware manufacturer to find problems with the hardware.
- Make sure that your SQL database is using the PAGE_VERIFY CHECKSUM option. If you encounter CHECKSUM errors, it’s very likely that database corruption occurred after SQL Server wrote pages to disk. Thus, check for issues with the disk system.