Share via

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

Analyst_SQL 3,551 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.
13,367 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
0 comments
Accepted answer
  1. Olaf Helper 43,246 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.

    2 people found this answer helpful.
    0 comments

1 additional answer

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

    0 comments