Error 5171 – mdf is not a primary database file

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.

How to troubleshoot Error 5171

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.

Read More on fixing corrupt databases

 SQL Server – The server is down and when to call for External Help

DBCC CHECKDB

SQL Server - Recover from a Suspect database

SQL Server - sys.dm_os_loaded_modules

 

Error_5171


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

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.

Working...

Post a comment on Error 5171 – mdf is not a primary database file


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer