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

by

Troubleshooting Guide: SQL Server Error 5009

Why This Error Happens

Error 5009 occurs when SQL Server attempts to attach, restore, or reference a database but cannot locate one or more of the underlying data (.mdf/.ndf) or log (.ldf) files at their expected file path. This is typically caused by files being moved, renamed, or deleted outside of SQL Server’s control, incorrect file paths specified in a CREATE DATABASE or RESTORE statement, or insufficient NTFS permissions for the SQL Server service account to access the directory. In some cases, the file header may also be corrupted, preventing SQL Server from initializing it even though the file physically exists.

Step-by-Step Resolution Guide

Step 1: Verify the Physical File Paths

  • Open File Explorer or PowerShell on the Windows Server 2025 / Windows 11 host and navigate to the expected data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
  • Confirm that the exact .mdf, .ndf, and .ldf files referenced in the statement actually exist in that location.
  • Run dir "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\*.mdf" in PowerShell to list all files and cross-check names.

Step 2: Check and Correct File Permissions

  • Right-click the DATA folder, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read, Write, and Modify permissions.
  • If permissions are missing, click Edit, add the service account, and grant Full Control to the folder and its contents.
  • Restart the SQL Server service afterward using net stop MSSQLSERVER followed by net start MSSQLSERVER.

Step 3: Correct the Statement or Reattach the Database

  • If running CREATE DATABASE ... FOR ATTACH or RESTORE DATABASE, ensure the FILENAME parameter in the MOVE clause matches the actual current file path.
  • Example corrected syntax:
  RESTORE DATABASE [SalesDB]
  FROM DISK = 'D:\Backups\SalesDB.bak'
  WITH MOVE 'SalesDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\SalesDB.mdf',
  MOVE 'SalesDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\SalesDB_log.ldf';
  • If files were moved to a new drive, update the path in the statement to reflect the new location rather than the original one.

Step 4: Rule Out File Corruption

  • Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORS once the database is online to check for corruption.
  • If the file header is damaged, restore from the most recent known-good backup instead of attempting to reattach the damaged file.

Alternative or Verification Steps

  • After applying fixes, run SELECT name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName'); to confirm all files show a state of ONLINE.
  • If the issue persists, use SQL Server Management Studio’s “Attach Database” dialog, which will visually flag any missing files, allowing you to manually browse to and reselect the correct file paths before confirming the attach operation.