SQL Server is one of the most deployed database server in organizations. The server enables easy storage and retrieval of data from application running on the same computer or other computers residing in the same network. However, due to some unfavorable conditions, the SQL Server database might end up in being corrupted. This in turn effects the availability of the database and its performance as well. The level of corruption underwent by the database depends on the error that the user encounters and lays a crucial role in deciding the possible chances of data recovery from corrupt database
In this blog, we will talk about one such error of SQL Server i.e. Error 5171. We will put forth the symptoms, the reason behind the error and the resolution with which the error can be removed in order to bring the database back in a consistent state.
Symptoms of SQL Error 5171
There are many symptoms which when seen, point in the direction of the error 5171. The user might encounter login failure of SQL Server and may not be able to restore SQL database files. In addition to this, the users might be unsuccessful in creating ‘tempdb’ database and face difficulty in attaching the database to the server.
The prototype of Error 5171 is:
C:Program FilesMicrosoft SQL ServerMSSQL 10_50.MSSQLSERVERMSSQLAria.mdf is not a primary database file.
Reasons Behind “mdf is not a primary database file”
Data in MDF files are stored as pages with each page occupying 8 KB size. The first page of the MDF file is referred as the header page containing crucial database information like file signature, size of the file, compatibility, etc. The actual data is stored in the rest of the pages.
In case the header page or other page is not recognized by the SQL Server, the entire MDF file is not considered as a valid file. This results in the Error 5171.
Scenarios When Error 5171 Is Encountered
There are multiple scenarios wherein a user comes across Error 5171. Some are:
1.At times when a user uses a mirror database in MS SQL Server and sets the database online by executing ALTER DATABASE command, the error is encountered.
Follow the below-mentioned procedure to fix the error:
- Set principal database.
- With ALTER DATABASE MODIFY FILE command, modify file information.
- Stop the SQL server instance.
- Copy MDF and LDF files in a different directory.
- Restart SQL Server.
- Attach both the MDF and LDF files.
Note:This process will also remove the database mirroring.
2.The error can be encountered at the time of upgrading SQL server to a new version. Suppose a user is using SQL Server 2008. The user detaches the database and then upgrades the server to the required version. After the up gradation is done, when the user tries to attach the database to the server, error 5171 can be encountered.
How to Fix SQL Server Error 5171?
In order to troubleshoot this error, use sp_attach_db command to attach the database. However, this method will only work if sp_detach_db has been used to detach the database.
With the help of above mentioned solutions, the user can easily remove this error. However, if the error as resulted in any damage to the database, that can be repaired with the help of other solutions and commercial utilities.