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

by

Why This Error Happens

Error 3172 occurs when a backup set or restore sequence references a filegroup that has been marked “defunct” in the database’s metadata, typically because the filegroup was removed or replaced in a later backup chain than the one being restored. This commonly happens when a filegroup is dropped and recreated with the same name, causing SQL Server’s internal filegroup ID tracking to mismatch between the backup media and the live database structure. It can also surface during piecemeal (partial) restores where the restore sequence skips a required intermediate log or differential backup that reconciles the filegroup state.

Step-by-Step Resolution Guide

Step 1: Verify the Filegroup and Backup History

  • Run RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourDB.bak' to inspect the backup set contents.
  • Query sys.master_files and sys.filegroups on the source database (if accessible) to confirm whether the filegroup was dropped, renamed, or recreated.
  • Check msdb.dbo.backupset and msdb.dbo.backupfilegroup tables to trace when the filegroup became defunct in the backup lineage.

Step 2: Rebuild the Restore Sequence in Correct Order

  • Perform a full database restore using WITH NORECOVERY to keep the database in a restoring state.
  • Apply all differential backups taken after the full backup, in chronological order, also using WITH NORECOVERY.
  • Apply every transaction log backup in sequence without skipping any files — missing even one log backup after the filegroup change is the most common cause of this error.
  • Example:
  RESTORE DATABASE YourDB FROM DISK = 'C:\Backups\Full.bak' WITH NORECOVERY;
  RESTORE DATABASE YourDB FROM DISK = 'C:\Backups\Diff.bak' WITH NORECOVERY;
  RESTORE LOG YourDB FROM DISK = 'C:\Backups\Log1.trn' WITH NORECOVERY;

Step 3: Exclude or Recreate the Defunct Filegroup

  • If the filegroup is confirmed obsolete, use RESTORE DATABASE ... WITH PARTIAL and specify only the active filegroups via the FILEGROUP = 'PRIMARY' clause.
  • For piecemeal restores, use RESTORE DATABASE YourDB FILEGROUP = 'FG_Active' FROM DISK = '...' WITH RECOVERY to bring only the needed data online while marking the defunct filegroup as offline.
  • If the filegroup must be preserved, restore from an earlier full backup taken before the filegroup was dropped, then replay all subsequent differentials and logs.

Step 4: Bring the Database Online

  • Once all backups are applied in the correct order, finish with RESTORE DATABASE YourDB WITH RECOVERY to complete the process and release the database from restoring mode.
  • Confirm no further log backups are pending application before executing this final command.

Alternative or Verification Steps

  • Run SELECT name, state_desc FROM sys.filegroups; after the restore to confirm the defunct filegroup is either absent or marked OFFLINE, and that all required filegroups show ONLINE.
  • If the error persists, restore the database to a new name (e.g., YourDB_Test) using the full backup chain in a lab environment to isolate exactly which backup file introduces the defunct filegroup reference before retrying on production.