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

akhter hussain 2,921 Reputation points
2021-05-05T23:05:19.333+00:00

i am facing below error,when i am attaching database

94060-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,167 questions
Accepted answer
  1. Olaf Helper 27,301 Reputation points
    2021-05-06T06:08:52.353+00:00

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Newest
Most helpful Newest Oldest
  1. Cris Zhan-MSFT 6,566 Reputation points
    2021-05-06T03:06:25.563+00:00

    Hi,

    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.
    https://www.easeus.com/sql-database-recovery/mdf-is-not-a-primary-database-file.html
    https://www.sqlserverlogexplorer.com/error-5171-mdf-is-not-a-primary-database-file/

    Update:
    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.