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

by

Troubleshooting Guide: SQL Server Error 3022

Why This Error Happens

This error occurs because SQL Server is attempting a file or filegroup backup on a database operating under the Simple Recovery Model, which does not support differential file backups on read-write filegroups. Under the Simple Recovery Model, SQL Server automatically truncates the transaction log, making it impossible to track the changes needed for a subsequent differential backup unless the filegroup is explicitly set to read-only. Essentially, the backup strategy being attempted is incompatible with the database’s current log management configuration.

Step-by-Step Resolution Guide

Step 1: Verify the Current Recovery Model

  • Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
  • Run the following query to check the status: SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';
  • Confirm if the result returns SIMPLE.

Step 2: Change the Recovery Model (If Full Backups are Required)

  • If you need standard differential backups without setting files to read-only, switch the database to the Full Recovery Model.
  • Execute: ALTER DATABASE YourDatabaseName SET RECOVERY FULL;
  • Note: This requires you to begin a regular schedule of transaction log backups to prevent the log file from growing indefinitely.

Step 3: Set the Filegroup to Read-Only (If Simple Model is Required)

  • If the Simple Recovery Model must be retained, you must manually set the specific filegroup to read-only before performing a differential file backup.
  • Execute: ALTER DATABASE YourDatabaseName MODIFY FILEGROUP YourFileGroupName READONLY;
  • Ensure no active transactions are writing to this filegroup before executing the command, as it will lock the group from further writes.

Step 4: Re-run the Backup Command

  • Attempt the file backup again using SSMS or T-SQL.
  • Example command: BACKUP DATABASE YourDatabaseName FILEGROUP = 'YourFileGroupName' TO DISK = 'C:\Backups\YourBackup.bak';
  • Check the “Messages” tab in SSMS to confirm the backup completed successfully without triggering Error 3022.

Alternative or Verification Steps

  • Verification: Run BACKUP DATABASE... WITH VERIFYONLY immediately after the backup completes to ensure the backup set is complete and readable.
  • Alternative: If read-only filegroups are not feasible for your business logic, switch to a standard Full Database Backup strategy instead of file/differential backups, as this does not require filegroup read-only status.