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

by

Troubleshooting Guide: SQL Server Error 4354 – File Left in Defunct State During Piecemeal Restore

Why This Error Happens

This error occurs because SQL Server 2025 does not support piecemeal (partial) restores when the database being restored requires an internal version upgrade. When a database backup originates from an older SQL Server version, the engine must upgrade the database schema and metadata immediately upon restore, but this upgrade process conflicts with the deferred, filegroup-by-filegroup nature of piecemeal restores. As a result, SQL Server marks the affected file as “defunct” to prevent inconsistent states, since it cannot safely bring online only part of a database that still needs a full version upgrade pass.

Step-by-Step Resolution Guide

Step 1: Verify the Source Database Version

  • Check the backup file’s originating SQL Server version using RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourDB.bak'.
  • Look at the SoftwareVersionMajor and SoftwareVersionMinor columns in the result set.
  • If the version is older than SQL Server 2025 (compatibility level below 170), piecemeal restore will not be supported for this backup.

Step 2: Perform a Full Restore Instead of Piecemeal

  • Abandon the RESTORE DATABASE ... FILEGROUP = 'PRIMARY' ... PARTIAL syntax for this specific backup.
  • Run a complete restore instead:
RESTORE DATABASE YourDB
FROM DISK = 'C:\Backups\YourDB.bak'
WITH RECOVERY,
MOVE 'YourDB_Data' TO 'C:\SQLData\YourDB.mdf',
MOVE 'YourDB_Log' TO 'C:\SQLData\YourDB.ldf';
  • Allow the restore to complete fully, letting SQL Server finish the internal upgrade process across all filegroups.

Step 3: Upgrade the Database on the Old Instance First

  • If piecemeal restore is a hard requirement, first restore the full backup on an instance running the original (older) SQL Server version.
  • Allow SQL Server to complete the upgrade process and bring the database to the current compatibility level on that intermediate instance.
  • Take a fresh full backup from this upgraded instance, then use that new backup file for the piecemeal restore on SQL Server 2025.

Step 4: Clear Defunct File References if Restore Already Failed

  • If the file is already stuck in a defunct state, remove the defunct filegroup reference:
ALTER DATABASE YourDB REMOVE FILE YourDefunctFileName;
  • Confirm no other filegroups depend on the defunct file before removal.
  • Re-attempt the restore using the full (non-piecemeal) method from Step 2.

Step 5: Check SQL Server Error Logs for Confirmation

  • Open C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (path varies based on instance name).
  • Search for Error 4354 entries to confirm the root cause matches the version upgrade conflict described above.
  • Cross-reference the timestamp with your restore attempt to validate the fix.

Alternative or Verification Steps

  • After completing a full restore, run SELECT state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDB'); to confirm all files show ONLINE instead of DEFUNCT.
  • If piecemeal restore remains a business requirement, consider standardizing all backup sources on SQL Server 2025 (or a matching compatibility level) before scheduling partial restores, eliminating the upgrade-in-progress conflict entirely.