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

by

Troubleshooting Guide: SQL Server Error 5104 – “File ‘%.*ls’ already used”

Why This Error Happens

Error 5104 occurs when SQL Server attempts to create or attach a database file using a physical file path that is already in use by another database, filegroup, or an existing file on disk. This typically happens due to a copy-paste error in a CREATE DATABASE or ALTER DATABASE statement where the file path wasn’t updated, or when restoring/attaching a database without specifying a unique MOVE path. It can also occur if a previous database using that file wasn’t properly detached or dropped, leaving the file locked or referenced in SQL Server’s internal metadata.

Step-by-Step Resolution Guide

Step 1: Verify the File Path in Your T-SQL Statement

  • Open the script that triggered the error (CREATE DATABASE, ALTER DATABASE, or RESTORE DATABASE).
  • Check the exact path specified in the FILENAME parameter, e.g., D:\SQLData\MyDB.mdf.
  • Confirm you are not reusing a path already assigned to another database by running: SELECT name, physical_name FROM sys.master_files;

Step 2: Check for Orphaned or Locked Files on Disk

  • Navigate to the target directory (e.g., D:\SQLData\ or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Use File Explorer or dir in Command Prompt to confirm if the .mdf/.ndf/.ldf file already exists.
  • If the file exists but isn’t tied to an active database, stop the SQL Server service via services.msc and manually rename or delete the orphaned file.

Step 3: Update the RESTORE Command with a Unique MOVE Path

  • If restoring a backup, explicitly redirect the logical file names to new physical paths using the WITH MOVE clause:
RESTORE DATABASE [NewDB]
FROM DISK = 'D:\Backups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO 'D:\SQLData\NewDB.mdf',
     MOVE 'MyDB_Log' TO 'D:\SQLLogs\NewDB.ldf';
  • Ensure the SQL Server service account has write permissions on the new target folder.

Step 4: Drop or Detach the Conflicting Database (If Applicable)

  • Identify which database currently owns the file: SELECT DB_NAME(database_id), physical_name FROM sys.master_files WHERE physical_name = 'D:\SQLData\MyDB.mdf';
  • If the database is obsolete, detach it safely: EXEC sp_detach_db 'OldDB';
  • Alternatively, drop it entirely if no longer needed: DROP DATABASE [OldDB];

Alternative or Verification Steps

  • After applying a fix, re-run your CREATE or RESTORE command and confirm the database appears correctly in SQL Server Management Studio (SSMS) under Databases without error.
  • If the issue persists, restart the SQL Server service (via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER) to release any stale file handles before retrying.