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

by

Troubleshooting Guide: SQL Server Error 12106 – Duplicate Database File Path

Why This Error Happens

Error 12106 occurs when SQL Server 2025 attempts to create, attach, or restore a database using a physical file path (.mdf, .ndf, or .ldf) that is already registered to another database on the same instance. This typically happens during a CREATE DATABASE, RESTORE DATABASE WITH MOVE, or ALTER DATABASE ADD FILE operation where the target filename was copied from another script without updating the logical or physical file name. It can also surface after a failed detach/attach cycle where SQL Server’s internal file metadata (sys.master_files) still references the old path even though the file appears orphaned on disk.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting File Registration

  • Run the following query to check which database currently owns the path in question:
  SELECT mf.name, mf.physical_name, d.name AS database_name 
  FROM sys.master_files mf 
  JOIN sys.databases d ON mf.database_id = d.database_id 
  WHERE mf.physical_name = 'D:\SQLData\YourFile.mdf';
  • Confirm whether the file is actively attached to a different database or is a leftover reference from a crashed operation.

Step 2: Rename or Redirect the Target File Path

  • If deploying a new database, change the FILENAME parameter in your CREATE DATABASE or RESTORE DATABASE T-SQL statement to a unique path (e.g., D:\SQLData\YourFile_New.mdf).
  • For restores, explicitly use the WITH MOVE clause to redirect both the data and log files:
  RESTORE DATABASE [YourDB] 
  FROM DISK = 'D:\Backups\YourDB.bak' 
  WITH MOVE 'YourDB_Data' TO 'D:\SQLData\YourDB_Data_New.mdf', 
  MOVE 'YourDB_Log' TO 'D:\SQLData\YourDB_Log_New.ldf';

Step 3: Clean Up Orphaned File Metadata

  • If the conflicting database was dropped improperly (file left on disk but metadata still cached), restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER on Windows Server 2025 to clear stale file handles.
  • Manually verify in File Explorer or via PowerShell (Get-ChildItem D:\SQLData\) that no duplicate .mdf/.ldf files exist with identical names in different subfolders mapped to the same logical path.

Step 4: Check for Lingering OS-Level File Locks

  • Use Resource Monitor (resmon.exe) on Windows 11/Server 2025 to confirm no other process (such as a backup agent or antivirus scanner) has the file handle open.
  • If a lock is found, terminate the locking process or exclude the SQL data directory from real-time antivirus scanning via Windows Defender settings.

Alternative or Verification Steps

  • Verify the fix by re-running the original CREATE DATABASE or RESTORE command; a successful execution with no Error 12106 confirms the path conflict is resolved.
  • As an alternative, use sp_detach_db followed by physically moving/renaming the conflicting file in Windows Explorer, then re-attach using CREATE DATABASE ... FOR ATTACH with the corrected, unique path.