[Solved] Microsoft SQL Server Database Error 5171 – MDF is not a primary database file

In this article, we will talk about different scenarios to repair the SQL Server when the error 5171 – MDF is not a primary database file, occurs and different alternatives to solve these problems.

SQL database corruption is generally coming with several problems. These problems affect database availability and performance on a high scale. The level of corruption in the database decides upon the actual chances of recovery and how you can effectively resolve the issue averting every risk of potential data loss.

Taking a particular case, you may encounter an SQL Server login failure or may run into a situation wherein you are unable to restore MS SQL database files. Another issue could be that you cannot create ‘tempdb’ database or every time you try to attach your database, you are unable to do so.

These problems are associated with SQL Error 5171 that states:

“database.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)”

This error primarily occurs due to invalid registry entries, power problems, virus infections or damaged drivers. The scenarios discussed below are very likely to cause SQL error 5171.

If you do not have a backup, or if the backup is corrupt, you may need to follow different steps to recover your data.

We will explain 2 different corruption scenarios in this article.

Case I: The database corruption in mirrored databases

Imagine that you have mirrored databases.

Suppose you have MS SQL Server 2016 installed in your system. While using a mirrored database, you attempt to set your database online by executing the following command and receive error 5171.

ALTER DATABASE mydb SET online

In this case, you may use the procedure given below to fix the issue:

• Set the database principal
• Modify the file information using ‘ALTER DATABASE MODIFY FILE’ command
• Stop the currently running instance of MS SQL Server

• Copy your MDF and LDF database files to another directory. You can check the path of the MDF and LDF files right-clicking the database and selecting properties in SSMS in the files page:

• Restart the SQL Server and then attach the database files.

Unfortunately, you will surely lose database mirroring after the process. You will need to configure the database mirroring again.

Case II

Suppose now, that you use MS SQL Server 2014. Now, you detach your database and upgrade to MS SQL Server 2016. After completing the installation, you try to attach the database again by adding the primary and secondary data files. In this process, you may encounter the below-given error message:

“M:\folder\file_1.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)”

As a resolution to the above problem, you can use ‘sp_attach_db’ for attaching the database.

sp_attach_db @dbname = N'mydb',
@filename1 =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\mydb_Data.mdf',
@filename2 =
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\ mydb_log.ldf';

You can also use the UI to attach a database:

However, this method won’t work, if you do not use ‘sp_detach_db’ for detaching the same database.

The following T-SQL sentence shows how to use the system procedure sp_detach_db:

EXEC sp_detach_db 'mydb', 'true';

This procedure detaches the database mydb.

Another possible remedy is to use ‘CREATE DATABASE’ command with the ‘FOR ATTACH’ clause.

The sentence used will be similar to this one:

CREATE DATABASE N'mydb'
ON (FILENAME = N'C:\MySQLServer\AdventureWorks_Data.mdf'),
(FILENAME = N'C:\MySQLServer\AdventureWorks_Log.ldf')
FOR ATTACH;

We create a database named mydb and I specify the data file and log file including the path. Finally, we use the FOR ATTACH option.

If problems still persist, you can use Stellar Repair for MS SQL to resolve and repair SQL error 5171.

Stellar Solution

Stellar Repair for MS SQL is a comprehensive tool that embraces a multitude of powerful repair mechanisms to effectively recover every lost, deleted or inaccessible object from the damaged SQL database. The software allows performing a precise recovery of tables, views, queries, stored procedures, indexes, user-defined functions, unique keys, foreign keys, Identities, Defaults, default constraints and User Defined Data Types. Moreover, it supports the latest SQL Server versions, including MS SQL Server 2019, 2017, 2016, 2014, 2012, 2008, 2005, 2000 and 7.0 and mixed formats.

It can recover errors of high severity and minor problems from MDF, NDF, and LDF files. This software requires an installer of less than 5 MB

The software requires you to stop MS SQL Server and move the MDF and LDF files to a different location and then you can restart the server and repair the copy of the database:

If you do not know the path of your database, you can use the search in the Folder button of the software:

You can also read SQL MVP, Grant Fritchey’s review from here.

Conclusion

In this article, we learned that the database can be corrupted in a database mirroring and when we attach a database in an upgraded SQL Server. We learned how to repair the database using SQL Server and using Stellar Repair for MS SQL.

This software is easy to learn and saves a lot of time.