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

by

Troubleshooting Guide: SQL Server Error 3171 — “File %ls is defunct and cannot be restored into the online database”

Why This Error Happens

Error 3171 occurs when SQL Server attempts a restore or rollforward operation on a database file that has been marked as defunct, meaning the file was removed from the filegroup (via ALTER DATABASE ... REMOVE FILE) prior to the backup being taken, but the backup set still references it. This commonly happens when a backup chain is applied out of order, a differential or log backup predates the file removal while a later full backup does not include it, or the restore sequence attempts to bring a dropped data file back online. Since the file no longer logically exists in the database’s current structure, SQL Server cannot reconcile it with the active filegroup metadata, causing the restore to fail.

Step-by-Step Resolution Guide

Step 1: Verify the Backup and File History

  • Query msdb.dbo.backupfile and msdb.dbo.backupset to determine when the file was marked as dropped (dropped_ls_number not null).
  • Run: SELECT file_id, logical_name, state_desc, dropped_ls_number FROM msdb.dbo.backupfile WHERE backup_set_id = <id>;
  • Confirm whether the defunct file belongs to an older backup than intended.

Step 2: Rebuild the Correct Restore Sequence

  • Ensure your restore plan starts from a full backup taken after the file was removed from the filegroup.
  • Skip full or differential backups that still reference the defunct file.
  • Use RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\Full_PostRemoval.bak' WITH NORECOVERY; to begin the chain from a clean baseline.

Step 3: Exclude the Defunct File from the Restore

  • If you must use a backup set containing the defunct file, explicitly exclude it using the MOVE or filegroup exclusion options in your RESTORE statement.
  • Run: RESTORE DATABASE [YourDB] FILEGROUP = 'PRIMARY' FROM DISK = 'D:\Backups\Full.bak' WITH NORECOVERY, PARTIAL;
  • Avoid issuing RESTORE commands that target the specific defunct logical file name directly.

Step 4: Apply Transaction Logs Carefully

  • Continue applying subsequent differential and log backups in strict LSN order using WITH NORECOVERY.
  • Run: RESTORE LOG [YourDB] FROM DISK = 'D:\Backups\Log1.trn' WITH NORECOVERY;
  • Finish with RESTORE DATABASE [YourDB] WITH RECOVERY; once all valid logs are applied.

Step 5: Validate Filegroup Consistency

  • After recovery, run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there are no orphaned filegroup references.
  • Check sys.database_files to ensure the defunct file is no longer listed.

Alternative or Verification Steps

  • Run SELECT name, state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDB'); to confirm all remaining files show ONLINE status with no defunct entries.
  • If the error persists, restore to a new database name using only post-removal backups, then rename it over the original once validated — this avoids any contamination from legacy backup sets referencing the defunct file.