How to Fix database.mdf is not a Primary Database File (SQL Server Error: 5171)?

There are different solutions, depending on the reason, to fix the MDF file is not a primary database in SQL Server. First, check the primary database file and its associated log files you’re trying to open or attach. Next, ensure you’re attaching the file with the same or higher SQL Server version. If the transaction log file is corrupted or damaged, then use the REBUILD LOG command to recreate it. If the MDF file itself is corrupted, repair it with the help of a professional SQL repair tool. In this article, we will explain these methods in detail to resolve the SQL error 5171.

What is SQL Error 5171?

You may encounter the SQL error 5171 while attaching MDF file/backup (.bak) file in your SQL server instance. It usually occurs when the server fails to read the MDF file or its transaction log file due to corruption. The error can take place in data mirroring and Always on Availability environment. It causes synchronization failure between replicas, preventing the secondary replicas from attaching or recovering the database.

To understand it better, I will try to reproduce this error.

I have a corrupt MDF file named AdventureWorksDW_Data.mdf and will try to attach it using the graphical interface of Microsoft SQL Server Management Studio.

As you can see, the attaching operation has failed with the error 5171.

When I clicked on Show Details, it displayed severity, state, and other information about the error that can help in troubleshooting.

SQL Server Error 5171- Causes

All data in the MDF file is stored in 8 KB pages. Each page includes a header with important system information, while the remaining pages contain actual data. If the header page or other pages are not recognized by the server, the MDF file is considered an invalid primary database, which leads to the error 5171. The error can be triggered by various other causes and factors. Common reasons include:

• MDF file is corrupted or damaged.
• You’re trying to open/attach MDF file without LDF file.
• Hardware problems on the system hosting the database (MDF) file.
• MDF file is not compatible with your MS SQL Server version.
• The format of file, you are trying to open, is incorrect.
• Invalid Registry entries.

What SQL server error MDF is Not a Primary Database File- Impacts

When the 5171 error occurs, you may face the following challenges:

• Database unavailability
• Increased downtime
• Attaching operation failure
• Synchronization failure (If database is connected on Always on Availability mode)
• Fail to set data mirroring

How to Fix SQL Error 5171 - MDF is Not a Primary Database File?

To resolve this error, follow the tested and tried methods given below.

 Method 1 - Check the MDF File you’re Trying to Attach

If you try to attach an incorrect or incompatible MDF file, then you can face the SQL Server error 5171 – MDF is not a primary database file. So, before opening the database in your current server instance, verify it and make sure:

• You are not trying to attach secondary file or an incomplete backup file.
• Check the file extension. It should be .MDF.
• You're using the correct file path.
• Make sure you’ve all permissions on MDF file that you’re trying to open.

Method 2 - Check SQL Server Version

One of the major reasons for the error 5171 is SQL Server version mismatch. Let’s take an example to understand this. If you try to attach an MDF file that was created in a higher server version like SQL Server 2019 to an older version (SQL Server 2014), then this error may occur. You can run the following command to check the current version of your SQL Server.

If the version is not compatible, then you can try using sp_attach_db command. This command can bypass the internal registration problems that occur during upgrades. It can sometimes help in this situation. Here is the syntax:

Method 3 - Rebuild the Log File

You can encounter the “MDF is not a primary database file” error while attaching database if the server fails to read the LDF file associated with the MDF file. This usually happens when the transaction log file is missing or corrupted. In such a case, you can use the ATTACH_REBUILD_LOG command to rebuild the log file. Here is how to use this command:

This command will create a new log file. You can also try other methods to attach SQL database without transaction log file.

Method 4 – Create New Database and Replace it with Affected One

You can even try forcing the SQL Server to recognize the MDF file path and its metadata, as a troubleshooting workaround, to resolve the error 5171. To do so, first, you have to create a new empty database with same name and structure. For instance, in my case, the database name is AdventureWorksDW_Data.mdf. Next, replace its MDF file with the corrupted one. Here’s how:

• Create a new database by executing the following command:

• This will create a fresh MDF and LDF with name identical to problematic one in the default data directory.
• Now stop your SQL server instance. Right-click on Server under Object Explorer and click Stop.

• Now, go to the database’s data folder which is same as default folder.
• Now copy your corrupted database file into this location.
• Restart SQL Server service and then start attaching the MDF and LDF files.

Method 5 - Restore and Repair MDF File

If you fail to attach the database in SQL Server due to MDF file corruption, then you can recover the database from backup. But to restore the backup file, it should be complete and accessible. To check this, you can run the RESTORE VERIFYONLY command.

If the backup is readable, then you can follow this blog to restore database from .bak file step-by-step. 

If the backup is corrupted, you may encounter backup restore errors.

In such a case, you can repair the MDF file using the DBCC CHECKDB command. But to use this command, the file should already be attached/online. In the situation, where the MDF file is not attached, Stellar Repair for MS SQL comes into the picture. You can use this reliable SQL repair tool to repair and restore MDF file in any condition or of any size.

It can recover indexes, stored procedures, tables, triggers, and other objects with absolute precision. Also, it saves the recovered data to a new database, live database, or in different file formats. The tool can help you resolve the attachment and inconsistency errors in SQL database, including the underlying error, with complete integrity.

To learn how to use Stellar SQL Recovery Tool to repair MDF file, watch this video.

Conclusion

If SQL Server fails to recognize MDF file as a primary database file, you may encounter the SQL Server error 5171 - MDF is not a primary database file. You can follow the methods discussed in this article to resolve the error. In case corruption in the MDF file or its header is behind this issue, then the best option is to use Stellar Repair for MS SQL. It can repair MDF file quickly and recover all the objects with complete integrity.