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

by

Troubleshooting Guide: SQL Server Error 1931 — Filegroup Offline

Why This Error Happens

Error 1931 occurs when one or more data files belonging to a filegroup cannot be accessed by SQL Server, causing the entire filegroup to be marked OFFLINE. This is typically caused by a missing, corrupted, or relocated physical file (.mdf/.ndf), an underlying storage/disk failure, or insufficient NTFS/service account permissions on the file path. It can also happen after an incomplete restore operation where secondary files were not properly recovered along with the primary filegroup.

Step-by-Step Resolution Guide

Step 1: Identify the Offline File(s)

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run the following query to check file states:
  SELECT name, physical_name, state_desc, database_id 
  FROM sys.master_files 
  WHERE state_desc <> 'ONLINE';
  • Note the exact physical_name path and database_id for the offline file.

Step 2: Verify File Existence and Permissions

  • Navigate to the physical file path noted above (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Confirm the .mdf or .ndf file actually exists at that location; if it was moved or deleted, this is your root cause.
  • Right-click the file/folder, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control or Modify permissions.
  • If the underlying disk/volume shows as disconnected in Windows Disk Management, reconnect or repair the storage volume first.

Step 3: Bring the File Back Online or Restore from Backup

  • If the file exists but was marked offline due to a transient I/O error, attempt to bring it back online:
  ALTER DATABASE [YourDatabaseName] 
  MODIFY FILE (NAME = 'YourLogicalFileName', ONLINE);
  • If the file is missing or corrupted beyond repair, restore it from the most recent full backup:
  RESTORE DATABASE [YourDatabaseName] 
  FILE = 'YourLogicalFileName' 
  FROM DISK = 'E:\Backups\YourBackup.bak' 
  WITH NORECOVERY;
  • Apply any subsequent differential or transaction log backups with WITH RECOVERY on the final log restore to bring the database to a consistent state.

Step 4: Restart SQL Server Service (If Needed)

  • Open services.msc on Windows Server 2025 / Windows 11.
  • Locate “SQL Server (MSSQLSERVER)” or the named instance, right-click, and select Restart.
  • Re-run the sys.master_files query from Step 1 to confirm the filegroup now reports ONLINE.

Alternative or Verification Steps

  • Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; after recovery to confirm there is no further corruption in the database.
  • If backups are unavailable or restore fails, use ALTER DATABASE ... SET EMERGENCY followed by DBCC CHECKDB with repair options as a last-resort alternative, understanding this may result in data loss.