Search sqlserver-dba.com

Follow sqlserver-dba.com

*Use the Comments section for questions

Daily & Exclusive Content

SQLServer-DBA.com Links

Error 5171 – mdf is not a primary database file

24 March,2014 by Tom Collins

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

Author: Tom Collins (http://www.sqlserver-dba.com)

Share:

Posted by Tom Collins at 9:31 AM in Backup and Restore, Disaster Recovery, Disk Storage, SQL Error Logs | Permalink | Comments (7) | TrackBack (0)

Searches: database corruption, error 5171, mdf header damage, mdf is not a primary database file

This error generally occurs due to invalid registry entries or damaged drivers of SQL server databases. Few months back, we were also experiencing this problem in our SQL 2005 server. We tried some manual methods but no luck then we had taken help from this third party tool which successfully fixed the error and restored databases: http://www.serversdatarecovery.com/sqldatabase.html

Now, it is working smoothly. Anyway, thanks for sharing the helpful guide!

Jerry

Posted by: Jerry Brown | Jul 15, 2015 6:52:51 AM

Nice post,

I tried a third party tool which successfully repaired my SQL database. Here is the link:

http://www.stellarinfo.com/sql-recovery.php

Posted by: Tom | Apr 7, 2016 1:10:59 AM

this informative video shows different methods to fix this error, must watch from here: https://www.youtube.com/watch?v=yt_DeOOErmk

Posted by: John Walker | May 6, 2016 12:40:57 AM

How to solved.
.mdf file is not a primary file.

No any database backup.

What is the solution.

Posted by: .mdf file is not a primary file. | Jul 22, 2016 11:11:31 AM

I tried to attach a database from a completely random file, and that failed with a different message number, May be the MDF file has got damaged due to file header corruption or wrong information in file header, unfortunately I have no backup. At last, I used SQL recovery application http://www.sqlrecoverytool.com/ which provides to export MDF file directly to the SQL Server.

Posted by: Jason Clark | Jun 14, 2017 1:32:56 AM

5171-mdf is not a primary database file the problem on the computer was working has no problem but i copied the files from sqlserver2000 to another pc sqlserver2005 the message 5171 is displayed no primary database file what is the problem i hope solution as fast as possible
thanks

Posted by: norai | May 6, 2018 12:07:16 PM

Hi @nori - have you double checked it's a valid backup? It may have corrupted on transfer. Check to see if you can restore on another server

Posted by: Jack Vamvas | May 8, 2018 3:59:24 AM

Verify your Comment

Previewing your Comment

Posted by: |

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

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.