SQL Server Error 5171: MDF Is Not A Primary Database File

Analyst_SQL 3,551 Reputation points

i am facing below error,when i am attaching database


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,918 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,564 questions
0 comments No comments
{count} votes

Accepted answer
  1. Olaf Helper 41,571 Reputation points

    Even if the file extension is MDF this doesn't mean the file is a SQL Server database file nor that's the primary database file, it could be a secondary as well.
    You have to get the right primary and all other files for the database.

    2 people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Cris Zhan-MSFT 6,611 Reputation points


    It looks like the MDF file corrupted or not recognized. The simplest alternative is to use the existing backup to restore the database if you have a backup of this database.

    If you cannot attach the previous database after upgrading SQL Server, try to attach the database using the sp_attach_db command.

    See also the following posts.

    After reading Olaf's answer, I tested that if a database has multiple data files with mdf extension, then if you use the mdf file that is not the primary data file to attach the database, the above error will indeed be reported.
    Usually the recommended file extension for secondary data files is .ndf.

    0 comments No comments