How do I fix the Failed to retrieve data for attaching mdf file is which not primary database file

Question

I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach it I get this error :

full message :

    ===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

Program Location:
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.GetOriginalDatabaseName(String primaryFilePath)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile.PopulatePrimaryFileData(String primaryFilePath, Boolean isXIPath)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabaseData.PrimaryFile..ctor(SqlManagementUserControl parent, CDataContainer dc, String fullPath, String databaseOwner, ServerConnection connectionInfo, Boolean isXIPath)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.AddPrimaryFile(String fullPath, String fileName, Boolean isXIPath)
   at Microsoft.SqlServer.Management.SqlManagerUI.AttachDatabase.buttonBrowse_Click(Object sender, EventArgs e)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.PrimaryFile.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
===================================
H:\GHAEM\neginsql.mdf is not a primary database file. (.Net SqlClient Data Provider)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-5171-database-engine-error

Server Name: SERVERX
Error Number: 5171
Severity: 16
State: 1
Line Number: 1

Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)

also I tried with the SQL Server 2008R2 , same error !

Answer 1

The dataset file extension is mdf. However, this might not be the primary database file, despite having an mdf extension. The database that you´re trying to attach might have multiple files with the mdf extension. In this case, you need to select the actual primary file. It is also possible that the mdf file is corrupt. If that´s the case, you´d need to restore from an existing database backup, rather than trying to attach the database files.