MDF is not a Primary Database File Error 5171.

Applies to: SQL Server Management Studio (SSMS), Microsoft SQL Server 2019.

Date created: September 16, 2023.
 

Problem Description.
 

You may receive the following error from SQL Server Management Studio (SSMS) when you try to attach a database to a SQL Server instance.

Microsoft SQL Server Management Studio
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
C:\Program Files\Microsoft SQL Server\MSSQL15\MSSQL\DATA\DatabaseName.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)

Cause.

The possible causes for this error are:

1. The primary database file (with .mdf extension) is corrupted or SQL Server is not able to recognize it.
2. You have multiple database files for the same database with the .mdf file extension. Only the real primary database file should have that extension.

Workaround/Solution.

Please make sure you specify to SQL Server the real primary data file. Always assign .ndf file extension to all secondary database files and leave the .mdf extension for the real primary data file.

If you have specified the real primary database file to SQL Server and still you get the error, then the database may be corrupted. The recommended action in that case is to restore from a backup the database, but if you cannot afford that then try the procedure explained by Paul Randal here to try to get the database attached using a trick and then proceed to repair it.
 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.