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.
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)
An exception occurred while executing a Transact-SQL statement or batch.
C:\Program Files\Microsoft SQL Server\MSSQL15\MSSQL\DATA\DatabaseName.mdf is not
a primary database file. (Microsoft SQL Server, Error: 5171)
The possible causes for this error are:
- The primary database file (with .mdf extension) is corrupted or SQL
Server is not able to recognize it.
- You have multiple database files for the same database with the .mdf
file extension. Only the real primary database file should have that
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