Troubleshooting Guide: SQL Server Error 3054 — Differential Backup Includes Read-Only Data

Why This Error Happens

This error occurs because databases using the Simple Recovery Model do not support traditional differential file backups that span both read-only and read-write filegroups. SQL Server 2025 restricts differential backups in this mode to prevent inconsistent log-dependent recovery scenarios, since simple recovery does not retain the transaction log history needed to reconcile changes across all filegroups. Essentially, SQL Server is signaling that your backup strategy needs to be scoped to only the writable portions of the database using a partial backup.

Step-by-Step Resolution Guide

Step 1: Identify the Recovery Model and Filegroup Status

• Open SQL Server Management Studio (SSMS) or a query window and run:

  SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• Check which filegroups are marked read-only by running:

  SELECT name, is_read_only FROM sys.filegroups;

• Confirm that the database is indeed in SIMPLE recovery mode, as this is the root trigger for Error 3054.

Step 2: Modify the Backup Script to Use READWRITEFILEGROUPS

• Update your T-SQL backup command to explicitly target only writable filegroups instead of performing a standard differential backup.
• Example corrected syntax:

  BACKUP DATABASE YourDatabaseName 
  READ_WRITE_FILEGROUPS 
  TO DISK = 'C:\SQLBackups\YourDatabaseName_Partial.bak' 
  WITH DIFFERENTIAL;

• Ensure the backup destination path (e.g., C:\SQLBackups\) exists on your Windows Server 2025 instance and that the SQL Server service account has write permissions to that directory.

Step 3: Update SQL Server Agent Jobs or Maintenance Plans

• If this backup is automated, navigate to SQL Server Agent > Jobs in SSMS.
• Locate the specific job step running the differential backup and replace the T-SQL command with the updated READ_WRITE_FILEGROUPS syntax from Step 2.
• Save the job and run a manual test execution to ensure the job step completes without throwing Error 3054.

Step 4: Evaluate Recovery Model Requirements (Optional Long-Term Fix)

• If full differential backup support across all filegroups is required, consider switching the database to the Full Recovery Model:

  ALTER DATABASE YourDatabaseName SET RECOVERY FULL;

• Be aware this requires a strategy for regular transaction log backups to prevent the log file from growing uncontrollably.

Alternative or Verification Steps

• To verify the fix, re-run the backup command manually in a query window; a successful execution will output a message confirming the number of pages processed without triggering Error 3054.
• If the error persists, check for stale filegroup metadata by running DBCC CHECKDB to rule out underlying corruption that might be misreporting filegroup read/write status.