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

by

Troubleshooting Guide: SQL Server Error 5170 – “Cannot Create File Because It Already Exists”

Why This Error Happens

Error 5170 occurs when SQL Server attempts to create a new database file (.mdf, .ndf, or .ldf) at a specified path, but a file with that exact name already exists in the target directory. This typically happens during database creation, restore operations, or file attachment when a previous database wasn’t cleanly dropped, leaving orphaned files behind. It can also occur if two databases are mistakenly configured to use the same physical file path, or if a prior failed operation left residual files that were never cleaned up.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting File Path

  • Open the full error message in SQL Server Management Studio (SSMS) or the SQL Server Error Log to locate the exact file path referenced (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf).
  • Navigate to that directory using File Explorer or PowerShell:
  Get-ChildItem "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\"
  • Confirm whether the file physically exists on disk.

Step 2: Verify the File Is Not In Use by Another Database

  • Run the following query to check if the file path is already registered to an active database:
  SELECT name, physical_name, state_desc 
  FROM sys.master_files 
  WHERE physical_name = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf';
  • If a database is actively using this file, choose a different file name or path for your new database/restore operation instead of overwriting it.

Step 3: Remove or Rename the Orphaned File

  • If the file is confirmed orphaned (not tied to any active database in sys.master_files), stop the SQL Server service temporarily:
  net stop MSSQLSERVER
  • Rename or delete the orphaned .mdf/.ldf file from the DATA directory.
  • Restart the SQL Server service:
  net start MSSQLSERVER

Step 4: Specify a Unique File Path in Your Script

  • When creating a new database or restoring a backup, explicitly define a new, non-conflicting file name using the MOVE option (for restores) or a custom path in CREATE DATABASE:
  RESTORE DATABASE MyDatabase 
  FROM DISK = 'C:\Backups\MyDatabase.bak'
  WITH MOVE 'MyDatabase' TO 'C:\SQLData\MyDatabase_New.mdf',
       MOVE 'MyDatabase_log' TO 'C:\SQLData\MyDatabase_New_log.ldf';
  • Ensure the new path does not already contain files with matching names.

Step 5: Check and Correct NTFS Permissions

  • Verify the SQL Server service account has read/write/delete permissions on the target DATA folder via right-click → Properties → Security tab.
  • Grant Full Control to the SQL Server service account (e.g., NT Service\MSSQLSERVER) if permissions are restrictive, as insufficient permissions can sometimes prevent proper file cleanup and cause stale file locks.

Alternative or Verification Steps

  • After applying the fix, re-run the CREATE DATABASE or RESTORE command and confirm it completes without Error 5170; then validate the database is online using SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase';.
  • If the error persists, use Process Explorer or Resource Monitor to check if another process (such as an antivirus scanner or backup agent) has a lock on the file, and temporarily exclude the SQL Server DATA directory from real-time scanning.