[Solved] SQL Server Database Error 5171 - MDF Is Not A Primary Database File

author icon

Tracy King updated on Aug 11, 2021 to MS SQL Database Recovery | How-to Articles

Applies to: 5171 error in MS SQL Server 2017, 2016, 2014, 2012, 2008, etc.

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

If you receive this error message, it means that you are having Database error 5171 on SQL Server:

"ABC.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)"

SQL Server error 5171.

Error 5171 is a common issue that many SQL Server administrators have met when they try to login SQL or restore the MS SQL database. This error may also happen when you can't create 'tempdb' dabase or can't attach MDF file in the database.

To resolve this error, you should check the causes first so to figure out the right solution:

  • MDF file corrupted or not recognized
  • Invalid registry entries
  • Virus infection
  • Power failure
  • Drivers damaged

If the 5171 error is caused by the latter four reasons, you are highly suggested to modify the registry entries, clean up virus and restart SQL, check the power connection, and update drivers.

If the problem exists in the MDF database file, read on to find two solutions here, getting rid of this issue. 

Solution 1. Repair MDF File with EaseUS SQL Recovery

When the MDF file corrupted or saved records lost in MDF file, you'll get this error on SQL. 

A fast and secure way to get rid of this issue is to repair the MDF file with a reliable SQL recovery tool. Notice that when you search online to find such a tool, choose the product provided by experienced company. Also, it should be risk-free, 100% clean, with a strong tech support team.

Being experienced in data recovery experience, EaseUS recently released its SQL recovery software to help SQL Server administrators.

With it, you can effortlessly repair the corrupted database, MDF file restore, and even restoring deleted records in SQL. 

Follow the steps to repair corrupted MDF file:

Step 1. Stop MS SQL Server service

Press "Windows + R" and enter services.msc.

Find and double-click "SQL Server (instant name)".

In the Properties windows, click "Stop" to end the SQL Server and click "Apply" to confirm.

Step 2. Run EaseUS SQL repair tool. In the main interface, click "Browse" (the two dots) to choose the corrupted MDF/NDF file. Then click "Repair" to start analyzing your MDF/NDF file. 

If you know the exact location of the file, click "Browse" to locate the database.

If you don’t know the file location, click "Search" to search for the .mdf or .ndf file in.

select the SQL database file

Step 3. When it has done, you will see all the database objects listed in the left pane of the window. Choose the database objects you'd like to repair and click "Export".

Select the database objects

Step 4. Choose a preferred way to export the database data: "Export to database" or "Export as SQL scripts". If you select "Export to database", you need to further select the server information, log into your account, and select a destination database, either a new one or an existing one.

export the SQL database objects

Step 5. Before you click "OK", now you need to restart the SQL Server.

Press "Windows + R" and enter services.msc.

Find and double-click "SQL Server (instant name)".

In the Properties windows, click "Start" to restart the SQL Server service and click "Apply" to confirm.

Step 6. Click "OK" to save the repaired files to your desired SQL database.

After saving the repaired MDF file, restart SQL Server and login with your password. Then you should be able to access the MDF data and run SQL Server normally again.

Solution 2. Manually Resolve SQL Server Error 5171

Another method that you can use to run Microsoft SQL Server Management Studio to resolve this error manually:

  • Step 1: Run Microsoft SQL Server Management Studio and make sure the database is online. 
  • If it’s offline, run the command: ALTER DATABASE mydb SET online.
  • Step 2: Modify MDF file information with ALTER DATABASE MODIFY FILE command.
  • Back up SQL Server database first before you continue with the next step.
  • Step 3: Right-click on the currently running SQL Server and select "Stop".
  • Fix SQL Server error 5171.
  • Step 4: Copy the MDF and LDF files to a different directory.
  • Manually fix mdf is not the primary file in SQL Server.
  • Step 5: After that, restart the SQL Server and attach both MDF and LDF files.

Tips to Protect SQL SQL Database 

After resolving the SQL database 5171 error, it’s also essential to learn useful tips to protect SQL Server database. 

Try the tips here for help:

1. Avoid improper upgrade of SQL Server

Don't upgrade SQL Server frequently. If the current version is running smoothly on your Server machine, stay with it.

If you've upgraded the SQL but are having an error like 5171, run sp_attch_db command to resolve this issue:

Fix SQL Server error 5171 due to imporper update

2. Back up SQL Server database regularly

Automated SQL Server backup software - EaseUS Todo Backup Advanced Server supports you to create a full backup of your huge SQL Server database with ease. 

3. Repair corrupted SQL Server database immediately

Once the database corrupted on SQL Server, apply EaseUS SQL Recovery to repair the database file and restore all valuable data immediately.

Was This Page Helpful?

 

EaseUS MS SQL Recovery

  • Restore (.mdf and.ndf) Files
  • Recover Corrupted Objects
  • Fix Database Log Files
TRY IT NOW