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

by

Troubleshooting Guide: SQL Server Error 3459 – Recovery Failed to Redo File Add

Why This Error Happens

Error 3459 occurs when the SQL Server transaction log contains a record of a file being added to the database (via ALTER DATABASE ADD FILE), but during the crash recovery or restore process, the engine cannot find the physical file at the expected path to replay that operation. This typically happens because the underlying .mdf, .ndf, or .ldf file was manually deleted, moved, or renamed outside of SQL Server after the ADD FILE transaction was committed to the log but before a checkpoint occurred. It can also surface in Windows Server 2025 environments where storage permissions (NTFS ACLs) or underlying disk/volume mappings changed between the time the log record was written and when recovery attempted to replay it.

Step-by-Step Resolution Guide

Step 1: Identify the Missing Physical File

  • Open the SQL Server Error Log (via SSMS > Management > SQL Server Logs, or ERRORLOG in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to find the exact file name and path referenced in the 3459 error message.
  • Use sqlcmd -S localhost -Q "RESTORE HEADERONLY FROM DISK = 'backup_path'" or check sys.master_files (if the instance is accessible in a limited state) to cross-reference the expected logical/physical file path.

Step 2: Restore or Recreate the Missing File

  • If the file was accidentally deleted, restore it from a Windows Server 2025 Volume Shadow Copy (VSS) snapshot or file-level backup to the exact original path (e.g., D:\SQLData\MyDB_NewFile.ndf).
  • If no backup of the physical file exists, create a placeholder file of the same logical name in the exact directory path cited in the error, as SQL Server only needs the file to exist to complete the redo operation; it will then allow you to properly resize or remove it afterward.
  • Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control NTFS permissions on the folder via Properties > Security in Windows Explorer or icacls D:\SQLData /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F.

Step 3: Retry Database Recovery

  • Take the database offline and back online via ALTER DATABASE [YourDB] SET OFFLINE; followed by ALTER DATABASE [YourDB] SET ONLINE; to force SQL Server to re-attempt crash recovery now that the file exists.
  • Alternatively, restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to trigger a fresh recovery cycle on startup.

Step 4: Remove the Orphaned File Reference (If Recovery Succeeds but File Is Unneeded)

  • Once the database is back online and stable, run ALTER DATABASE [YourDB] REMOVE FILE [LogicalFileName]; to cleanly drop the dummy/placeholder file reference from the database metadata.
  • Verify there is no critical data expected in that file by checking the file group assignment via sys.database_files.

Alternative or Verification Steps

  • Confirm the fix by querying SELECT state_desc, name, physical_name FROM sys.master_files WHERE database_id = DB_ID('YourDB'); — the state should read ONLINE for all files with no errors in the SQL Server Error Log.
  • If the placeholder file approach fails due to transaction log corruption, restore the entire database from the last known good full backup plus transaction log backups (RESTORE DATABASE ... WITH NORECOVERY then RESTORE LOG ... WITH RECOVERY) to bypass the corrupted redo operation entirely.