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

by

Troubleshooting Guide: Error 3149 in Microsoft SQL Server 2025

Why This Error Happens

Error 3149 occurs when attempting a RESTORE operation with the RECOVER_DATA_ONLY option against a file or filegroup that is not currently in the OFFLINE or RECOVERY_PENDING state. This typically happens because the target filegroup is still ONLINE, has already been recovered, or the database is in a state (such as RESTORING with a pending log chain) that does not permit this specific piecemeal restore operation. It can also surface when the sequence of a piecemeal restore is executed out of order, or when a previous restore command silently failed, leaving the filegroup in an unexpected status.

Step-by-Step Resolution Guide

Step 1: Verify the Current State of the Filegroup

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
  • Run the following query to check the exact status of your files:
SELECT name, state_desc, file_id 
FROM sys.master_files 
WHERE database_id = DB_ID('YourDatabaseName');
  • Confirm whether the specific file or filegroup mentioned in the error is actually ONLINE rather than OFFLINE or RECOVERY_PENDING.

Step 2: Correct the Restore Sequence Order

  • If you are performing a piecemeal restore, ensure you have restored the Primary filegroup first using PARTIAL syntax before attempting to recover secondary files.
  • Do not use RECOVER_DATA_ONLY unless the filegroup is strictly OFFLINE. If the filegroup is already online, you likely do not need this specific command.
  • If the file is in a RESTORING state (mid-process), complete the standard restore sequence (Full → Differential → Log) before attempting data-only recovery.

Step 3: Take the Filegroup Offline Manually (If Required)

  • If the filegroup is stuck in an inconsistent state (e.g., RECOVERY_PENDING due to a previous crash) but SQL Server isn’t recognizing it correctly, you may need to force it offline.
  • Navigate to the SQL Server Configuration Manager to ensure the SQL Server service (MSSQLSERVER) on Windows Server 2025 is running without resource contention that might be freezing the I/O state.
  • Execute the following T-SQL to attempt to set the file offline manually if corruption is suspected:
ALTER DATABASE YourDatabaseName MODIFY FILE (NAME = YourLogicalFileName, OFFLINE);
  • Note: This step should only be used if data loss for that specific secondary file is acceptable or already expected.

Step 4: Check for Concurrent Restore Sessions

  • Ensure no other session or script (such as an Ola Hallengren maintenance job or a third-party backup agent) is simultaneously trying to interact with the same database file.
  • Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for concurrent locking issues or Event ID conflicts that might be blocking the state change.

Alternative or Verification Steps

  • Verify the Fix: After restoring the primary filegroup and before running the final recovery, re-run the sys.master_files query from Step 1. The state_desc for the target file should explicitly read RECOVERY_PENDING before you attempt the RECOVER_DATA_ONLY command again.
  • Alternative Solution: If the filegroup state remains stuck despite a clean restore sequence, consider dropping the specific corrupted secondary file (if the database schema allows for data loss on that filegroup) and restoring the database using a WITH RECOVERY flag on the primary filegroup only, effectively isolating the corrupted secondary data.