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

by

Troubleshooting Guide: Error 2529 – Filegroup %.*ls is Offline

Why This Error Happens

Error 2529 occurs when SQL Server 2025 cannot access one or more data files belonging to a filegroup, causing the entire filegroup to be marked OFFLINE to protect database integrity. This is typically caused by missing, corrupted, or inaccessible underlying files (e.g., deleted NDF/MDF files, disconnected storage, or disk I/O failures). It can also result from insufficient NTFS permissions for the SQL Server service account on the file path, or from an improper shutdown that left the filegroup in a suspect state.

Step-by-Step Resolution Guide

Step 1: Verify File and Filegroup Status

  • Run the following query to identify the affected files and their physical paths:
  SELECT name, physical_name, state_desc 
  FROM sys.master_files 
  WHERE database_id = DB_ID('YourDatabaseName');
  • Confirm which filegroup is offline by checking sys.filegroups and sys.database_files.
  • Note the exact physical_name path reported for the offline file.

Step 2: Check Physical File Accessibility

  • Navigate to the reported path (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) in Windows Explorer or via PowerShell:
  Test-Path "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourFile.ndf"
  • If the file is missing, check backup storage or SAN/NAS mount points for disconnection issues.
  • If the file exists, verify it is not marked Read-Only or flagged by Windows Defender/Antivirus as quarantined.

Step 3: Validate NTFS Permissions for SQL Service Account

  • Right-click the DATA folder → Properties → Security tab.
  • Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain service account) has Full Control over the file and parent directory.
  • Apply permissions using PowerShell if GUI access is restricted:
  icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T

Step 4: Bring the Filegroup Back Online

  • If the file is accessible and permissions are corrected, attempt to bring the filegroup online:
  ALTER DATABASE YourDatabaseName 
  MODIFY FILEGROUP YourFileGroupName ONLINE;
  • If the file was lost or corrupted beyond repair, restore the specific file from the most recent full/differential backup using RESTORE DATABASE ... FILE = 'YourFile' WITH NORECOVERY, followed by a log restore.

Step 5: Restart SQL Server Service (If Required)

  • If permission changes do not take effect immediately, restart the SQL Server service via Services.msc or:
  Restart-Service -Name MSSQLSERVER -Force
  • Re-run the Step 1 query to confirm the filegroup state has changed from OFFLINE to ONLINE.

Alternative or Verification Steps

  • Verify Resolution: Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; to ensure no further corruption exists within the filegroup after bringing it online.
  • Alternative Fix: If the physical file is permanently lost and no backup exists, consider using ALTER DATABASE ... MODIFY FILE to redirect the logical file to a new physical location, followed by a restore sequence from the last known good backup.