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

by

Troubleshooting Guide: Error 4217 – “BACKUP LOG cannot modify the database because the database is read-only”

Why This Error Happens

This error occurs because SQL Server attempts to truncate the transaction log as part of a log backup operation, but the database is currently set to READONLY mode, preventing any write operations including log truncation markers. This commonly happens after restoring a database with the READONLY option, attaching a database from a read-only filesystem, or when a database is intentionally set to read-only for reporting purposes but log backup jobs were not excluded from maintenance plans. Despite the error, SQL Server typically continues the backup operation but skips the log truncation step, which can lead to unexpected log chain behavior.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

  • Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the SQL Server 2025 instance.
  • Run the following query to check the database status:
  SELECT name, is_read_only, recovery_model_desc 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  • Confirm whether is_read_only returns 1, validating the root cause.

Step 2: Determine Intent (Permanent vs. Temporary Read-Only)

  • If the database was intentionally set to read-only (e.g., for a reporting replica), exclude it from transaction log backup jobs in SQL Server Agent.
  • Navigate to SQL Server Agent > Jobs in SSMS and edit the maintenance plan or backup job step to skip read-only databases using a conditional check.
  • If the read-only state was unintentional, proceed to Step 3 to revert it.

Step 3: Set the Database Back to READ_WRITE

  • If write access and proper log backups are required, change the database mode using Transact-SQL:
  ALTER DATABASE YourDatabaseName 
  SET READ_WRITE WITH NO_WAIT;
  • Ensure no active connections are blocking the change; if necessary, use WITH ROLLBACK IMMEDIATE to force disconnect active sessions before altering the mode.
  • Verify the file permissions on the physical .mdf/.ldf files located typically in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ to ensure the Windows Server 2025 OS level permissions are not forcing a read-only file attribute.

Step 4: Update Maintenance Plans and Backup Scripts

  • Modify existing SQL Server Agent jobs or Maintenance Plan Wizard tasks to filter out read-only databases explicitly if they are meant to remain in that state long-term.
  • Use a dynamic script check before the backup command:
  IF (SELECT is_read_only FROM sys.databases WHERE name = 'YourDatabaseName') = 0
  BEGIN
      BACKUP LOG YourDatabaseName TO DISK = 'YourBackupPath.trn'
  END

Alternative or Verification Steps

  • To verify the fix, manually execute a BACKUP LOG command against the specific database and confirm the operation completes without Error 4217 in the SQL Server Error Log or Windows Event Viewer.
  • If the database must remain strictly read-only for compliance reasons, switch the recovery model to SIMPLE to prevent the system from attempting log backups entirely, using ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE;.