Error 5171 of MSSQL Server database


Repair & Resolve MSSQL Error 5171


Home >> Repair MSSQL Error5171


How to resolve error 5171 of MSSQL Server database


SQL database corruption is generally accompanied by various problems. These problems affect database availability and performance on a high scale. The level of corruption in the database decides upon the actual chances of recovery and how you can effectively resolve the issue averting every risk of potential data loss.


Taking a particular case, you may encounter an SQL Server login failure or may run into a situation wherein you are unable to restore MS SQL database files. Another issue could be that you cannot create 'tempdb' database or every time you try to attach your database, you are unable to do so. These problems are associated with SQL Error 5171 that states:


"Not a primary database file"


This error primarily occurs due to invalid registry entries or damaged drivers. The scenarios discussed below are very likely to cause SQL error 5171.


Case I

Suppose you have MS SQL Server 2005 installed in your system. While using a mirrored database, you attempt to set your database online by executing the following command and receive error 5171.


"ALTER DATABASE mydb SET online"


In this case, you may use the procedure given below to fix the issue:

  • Set the database principal.
  • Modify the file information using 'ALTER DATABASE MODIFY FILE' command.
  • Stop the currently running instance of MS SQL Server. Copy your MDF and LDF database files to another directory.
  • Restart the SQL Server and then attach the database files.


But, you will surely lose database mirroring after the process.


Case II

Suppose you use MS SQL Server 2005. Now, you detach your database and upgrade to MS SQL Server 2008. After completing the installation, you try to attach the database again by adding the primary and secondary data files. In this process, you may encounter the below given error message:


"M:\ABCDATA2\ABC_1.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)"


As a resolution to the above problem, you can use 'sp_attach_db' for attaching the database. However, this method won't work, if you do not use 'sp_detach_db' for detaching the same database. Another possible remedy is to use 'CREATE DATABASE' command with the 'FOR ATTACH' clause.


If problems still persist, you can use Stellar Phoenix SQL Recovery to resolve and repair SQL error 5171. Stellar Phoenix SQL Recovery is a comprehensive tool that embraces a multitude of powerful repair mechanisms to effectively recover every lost, deleted, or inaccessible object from the damaged SQL database. The software allows performing a precise recovery of tables, views, queries, stored procedures, indexes, user-defined functions, etc. Moreover, it supports the latest SQL Server versions, including MS SQL Server 2008, 2005, 2000, and 7.0