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

akhter hussain 2,491 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.
8,586 questions
No comments
{count} votes

Accepted answer
  1. Olaf Helper 25,906 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.

    No comments

1 additional answer

Sort by: Most helpful
  1. Cris Zhan-MSFT 6,561 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.

    No comments