Question: I’m getting a SQL Server Error 5171 error , when trying to attach an mdf. The message is "mdf is not a primary database file". How can I fix this issue?
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) D:\Data\mydb.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)
Answer: My initial assumption is the database file is damaged. Confirming this assumption might be difficult.
1) Check to see you have a valid backup. If so , restore from the backup device. Read more on disaster recovery. When you confirm there is a valid backup , discuss with business owners and operations staff the timelines for recovery.
This is a critical step , as it is highly likely you will not be able to recover the file and to minimise downtime , preparing for a RESTORE could save you critical time.
2) Another potential source of recovery may be mirrored drives as used in SAN replication. For example , if there is an asynchronous mirroring setup – then ensuring you can stop the mirroring , then failover to the mirror drives may allow access to a more recent version than the backup version
3) Use a third party utility. Usually the third party utilities promise you everything including solving third world poverty, but in reality it make take testing a few different ones – and hopefully one can fix the issue
4) Fix the mdf header s. I’ve only added this as an option – very few individuals could probably manage this step. As well as very in-depth technical knowledge , it would require some time.