How to fix Error 5171 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 5171 – “%.*ls is not a primary database file”

Why This Error Happens

This error occurs when SQL Server attempts to attach or restore a database but the file specified as the primary data file (.mdf) is either corrupted, mislabeled, or is actually a secondary (.ndf) or log (.ldf) file being passed in the wrong parameter. It can also happen if the file header has been damaged due to improper shutdown, disk corruption, or if a non-SQL file (with an .mdf extension) is being referenced. Additionally, this can surface if the database file was edited or truncated by an external process, corrupting the file header signature that SQL Server uses to identify it as a primary file.

Step-by-Step Resolution Guide

Step 1: Verify the File Being Attached is Correct

  • Confirm you are pointing to the actual primary .mdf file and not an .ndf or .ldf file in your CREATE DATABASE ... FOR ATTACH or sp_attach_db command.
  • Check the file path, e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDB.mdf.
  • Right-click the file in Windows Explorer and confirm the file size is non-zero and matches expected backups.

Step 2: Inspect the File Header for Corruption

  • Use a hex editor or PowerShell to check the first few bytes of the file for the standard SQL Server page header signature.
  • Run the following command to attempt a header-only validation:
  DBCC CHECKPRIMARYFILE ('C:\Path\To\YourDB.mdf', 2)
  • If the command returns errors or fails silently, the file header is likely corrupted and the file cannot be used as-is.

Step 3: Restore from a Known Good Backup

  • Navigate to your backup directory and locate the most recent .bak file.
  • Use SQL Server Management Studio (SSMS) or T-SQL to restore the database:
  RESTORE DATABASE YourDB
  FROM DISK = 'C:\Backups\YourDB.bak'
  WITH REPLACE, RECOVERY;
  • Ensure the SQL Server service account has read/write permissions on both the backup folder and the destination data folder.

Step 4: Rebuild Using Emergency Mode (If No Backup Exists)

  • As a last resort, attempt to bring the database online using emergency mode repair:
  ALTER DATABASE YourDB SET EMERGENCY;
  DBCC CHECKDB (YourDB, REPAIR_ALLOW_DATA_LOSS);
  • Be aware this step may result in data loss and should only be used when no valid backups are available.

Step 5: Validate File System and Disk Integrity

  • Run chkdsk C: /f (replace with the appropriate drive letter) to rule out underlying disk corruption on Windows Server 2025 or Windows 11.
  • Check Windows Event Viewer under Windows Logs > System for any disk-related warnings (Event ID 7 or 11) that may indicate hardware-level issues affecting the file.

Alternative or Verification Steps

  • After attaching or restoring, run SELECT name, state_desc FROM sys.databases WHERE name = 'YourDB'; to confirm the database state reads ONLINE.
  • If attachment still fails, consider using the sp_attach_single_file_db stored procedure as an alternative, which forces SQL Server to rebuild the transaction log while using only the primary data file.