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

by

Troubleshooting Guide: SQL Server Error 3410 – “Data in filegroup %s is offline”

Why This Error Happens

This error occurs when SQL Server marks a filegroup as OFFLINE, typically due to a failed or interrupted database recovery process affecting one or more data files within that filegroup. It can also result from missing, corrupted, or inaccessible physical data files (.ndf/.mdf) on the Windows Server 2025 or Windows 11 file system, often caused by disk failures, improper shutdowns, or permission issues for the SQL Server service account. Additionally, restoring a database without including all filegroups, or attempting operations on a filegroup that failed during an autogrow operation, can trigger this state.

Step-by-Step Resolution Guide

Step 1: Verify Filegroup and File Status

  • Run the following query to identify the affected filegroup and file state:
  SELECT name, state_desc, physical_name 
  FROM sys.master_files 
  WHERE database_id = DB_ID('YourDatabaseName');
  • Check Windows Event Viewer (Event Viewer > Windows Logs > Application) for related I/O or disk errors correlating with the SQL Server error log timestamp.
  • Locate the SQL Server error log via C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the exact file path reported as offline.

Step 2: Check File System and Permissions

  • Navigate to the physical file location (e.g., D:\SQLData\YourDB_Data2.ndf) and confirm the file exists and is not corrupted or renamed.
  • Right-click the file/folder, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions.
  • If the underlying disk or volume is disconnected (common in virtualized Windows Server 2025 setups), remount the volume using Disk Management (diskmgmt.msc) and ensure the drive letter matches the path in sys.master_files.

Step 3: Bring the Filegroup Back Online

  • If the file is present and accessible, attempt to bring the database online, which should also recover the filegroup:
  ALTER DATABASE YourDatabaseName SET ONLINE;
  • If only the secondary filegroup is offline, try restoring just that filegroup from a valid backup:
  RESTORE DATABASE YourDatabaseName 
  FILEGROUP = 'YourFilegroupName' 
  FROM DISK = 'D:\Backups\YourDB.bak' 
  WITH NORECOVERY;
  RESTORE LOG YourDatabaseName FROM DISK = 'D:\Backups\YourDB_log.trn' WITH RECOVERY;

Step 4: Rebuild or Replace Missing Data Files (If File is Lost)

  • If the physical file is permanently lost or corrupted beyond repair, use emergency mode repair as a last resort:
  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS);
  • Note this may result in data loss for objects stored in that specific filegroup.

Alternative or Verification Steps

  • Verify the fix by re-running SELECT state_desc FROM sys.database_files; to confirm the filegroup now shows ONLINE instead of OFFLINE.
  • If the restore process fails due to backup corruption, use RESTORE VERIFYONLY FROM DISK = 'D:\Backups\YourDB.bak' to validate backup integrity before attempting the restore again.