24 March,2014 by Jack Vamvas
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.
This step highlights the importance of a solid backup strategy supporting Recovery Point Objective
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.
SQL Server – The server is down and when to call for External Help
SQL Server - Recover from a Suspect database
SQL Server - sys.dm_os_loaded_modules
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |