How to Resolve SQL Server 'Database cannot be Opened' Issue Due to MDF Corruption?

Master Database File (MDF) is the primary database file in MS SQL Server that stores all the data, including views, tables, stored procedures, foreign keys, and primary keys. Sometimes, while opening the MDF file, you may face an issue where the SQL Server database cannot be opened and encounter an error, like the one given below:

• SQL Server error 5171: MDF is not a primary database file
• Unable to open the file xxxxx.mdf. Operating system error 5: (Access is denied)”
• Event ID 824: SQL Server detected a logical consistency-based I/O error
• Msg 8114, Level 16, State 5, Line 2: Error converting data type varchar to numeric

Such errors usually occur when there is corruption in the MDF file. In this article, we'll explain the possible reasons behind the corruption of MDF files and show how to repair corrupted MDF files and resolve the issue.

Causes of Corruption in the MDF File

There are various reasons that can lead to corruption in the SQL Server database (MDF) file. Some common ones are given below:

MS SQL Server or System Crash

MS SQL Server or system may crash due to issues with the operating system, hardware, or software. If the server or system crashes when working on the MDF file, it can damage or corrupt the file.

Abrupt System Shutdown

Sudden power failure or forced shutdown of the system while the SQL Server is running can corrupt the MDF file stored on your system.

Malware or Virus Attacks

Malware or viruses can also damage MDF files and make them inaccessible. It usually occurs when your system does not have an antivirus or anti-malware protection software.

Issues With Storage Drive

Bad sectors on the storage drive or file system errors can corrupt the SQL database files. If the drive is damaged, then it can also cause corruption in the MDF files.

Methods to Resolve 'Database cannot be Opened' Issue Due to MDF File Corruption

If your MDF file is corrupted, you can then follow the below-mentioned methods to repair and recover the corrupt MDF file, thus resolving the “Database cannot be opened” issue in SQL Server.

Method 1: Restore the MDF File From Backup 

If you have created a backup of your MDF file, then you can restore the file from the backup. But before restoring, you need to check if your backup is complete and readable. For this, you can use the RESTORE VERIFY ONLY command. If this command shows a success message, then you can proceed to restore the backup. For this, use the below Transact SQL command:

BACKUP DATABASE [AdventureWorks2019] TO DISK = N’C:\backups\AdventureWorks2019.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorks2019-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Alternatively, you can use the SQL Server Management Studio (SSMS) to restore the backup.

Method 2: Use DBCC CHECKDB Command

If the backup is not updated or there is a problem while restoring the backup file, then you can use the DBCC CHECKDB command to repair the corrupt MDF file. To repair the database file, you need to make sure that you have admin rights on the database. Before repairing, set the database to single-user mode by using the below command:

ALTER DATABASE Dbtesting SET SINGLE_USER

If you are unable to set the database to SINGLE_USER mode, then verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. 

Next, run the DBCC CHECKDB command as given below to repair the database (MDF) file: 

DBCC CHECKDB (N ’Dbtesting’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS

GO

When the database is repaired, set it to MULTI_USER mode again by using the below command: 

ALTER DATABASE Dbtesting SET MULTI_USER

Note: The DBCC CHECKDB command can help you repair the MDF file. However, while repairing the file, it may deallocate pages or rows, which can result in data loss. 

Method 3: Use a Professional SQL Repair Tool

Repairing the SQL database using the DBCC CHECKDB command with the REPAIR_ALLOW_DATA_LOSS option can result in data loss. To avoid data loss, you can use a powerful MS SQL repair software, like Stellar Repair for MS SQL. This software can repair corrupt MDF files and restore all the file objects, like primary keys, tables, triggers, views, etc., without changing the original structure. It saves the repaired file data in a new MDF file and various other formats, such as CSV, HTML, and XLS. The software supports SQL database (MDF and NDF) files created on Windows and Linux systems. 

To Conclude

The 'Database cannot be opened' issue in MS SQL Server can occur due to corruption in the MDF file. To fix this issue, the easiest way is to restore the MDF file from the last backup. If the backup file is obsolete or not working, you can use the DBCC CHECKDB command to repair the corrupt MDF file. If the DBCC CHECKDB command fails to repair the database, then use a powerful SQL repair tool, like Stellar Repair for MS SQL. It can repair corrupt MDF files and restore all the data to a new MDF file with complete integrity. You can install the free version of Stellar Repair for MS SQL to scan the corrupt MDF file and preview the recoverable data.