Sometimes, when trying to attach a database in SQL Server, you receive an error ‘MDF is not a primary database file (error 5171).’ For a complete error message, check the following screen:
What Causes SQL Server Error 5171: MDF is Not a Primary Database File?
According to Microsoft, each database has one primary MDF data file, but it doesn’t mean it can have one MDF file. A SQL database can have multiple data files with a .mdf extension. However, only one can be the primary data file.
The error message ‘MDF is not a primary database file’ could occur if the file you are trying to attach is not the primary file. If this is not the case, then your database file may be corrupt.
How to Fix SQL Server Error 5171: MDF is Not a Primary Database File?
Try implementing the methods in the same sequence below to fix the error. If a method fails to resolve the SQL Server error 5171, proceed with the next one.
Method 1 – Attach Primary Database File
Check if you’re attaching the primary database (MDF) file to the SQL Server. If not, attach the primary MDF file, along with secondary files (.ndf/.ldf) to the server.
Method 2 – Use ‘sp_attach_db’ to Attach the Database
Note: This method will only work if the database you are trying to attach was previously detached from the server using ‘sp_detach_db.’
Use the stored procedure ‘sp_attach_db’ to attach the database. For this, execute the following T-SQL query in the SQL Server Management Studio (SSMS):
USE master GO EXEC sp_attach_db @dbname = 'DB_Name', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\ DB_Name _Data.mdf', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\ DB_Name _log.ldf';
Now go to the DATA folder where the database is stored and check for .mdf/.ndf files. After ensuring that the folder contains the correct primary and secondary database files, attach the database to your SQL Server instance.
Note: The ‘sp_attach_db’ feature will be removed from future SQL Server versions. In that case, use CREATE DATABASE database_name FOR ATTACH command.
Method 3 – Restore from FULL Database Backup
Note: Skip to the next method if you do not have an updated backup or corrupt.
If you’re still getting the ‘.mdf is not a primary database file’ error, the chances are that the file is corrupt. So, try restoring the file from the last known good backup and then attach the database to the server.
The steps to restore a DB from backup are as follows:
- Open SSMS and connect to an instance of your SQL Server.
- Under Object Explorer, expand Databases.
- From the list of databases, right-click on the database you want to restore, and then click Restore Database.
- On the ‘Restore Database’ screen, under Source, click on the button next to Device in the General section.
- Click the Add button in the ‘Select backup device’ dialog box.
- Choose the backup (.bak) file you want to restore and click OK.
- On the ‘Restore Database’ screen, enter the database name to be restored and click OK to continue.
Once the database is restored, try to attach it to the SQL Server.
Method 4 – Repair the MDF File
If none of the above methods works, the MDF file may be corrupt. So, you must try repairing the file by running the DBCC CHECKDB command with the recommended repair option (like ‘REPAIR_FAST,’ ‘REPAIR_REBUILD,’ or ‘REPAIR_ALLOW_DATA_LOSS’).
Remember, executing DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS involves risk of data loss. A better alternative is to use a SQL repair tool, like Stellar Repair for MS SQL. The tool helps fix corruption in the database by repairing the database MDF file without modifying the stored data structure. Once the file is repaired, try attaching it to the SQL Server.
Conclusion
You may get the SQL error 5171 ‘MDF file is not a primary database file’ when attempting to attach a database. This usually happens when the DB file you are attaching to the server is not the primary database file. It could be any other file with an .mdf extension. So, the 5171 error may get resolved by attaching the correct primary database file. If this is not the case, your server might be unable to recognize the file due to corruption. In such a situation, repair your file and then try attaching it.