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

by

Troubleshooting Guide: SQL Server Error 3240 – Mirrored Media Set Append Failure

Why This Error Happens

Error 3240 occurs when a SQL Server BACKUP command targets a mirrored media set but the T-SQL script does not reference every physical device (mirror) that was originally used to create that media set. SQL Server mirrored backups require strict symmetry—if the set was initialized with two, three, or four mirrors, every subsequent append operation must explicitly list all of those same file paths in the same order. This typically happens when a mirror device is removed, renamed, moved to a different drive, or omitted due to a copy-paste error in a maintenance script or SQL Agent job step.

Step-by-Step Resolution Guide

Step 1: Identify the Original Media Set Configuration

  • Query msdb.dbo.backupmediafamily and msdb.dbo.backupmediaset to determine how many mirrors were used when the set was first created.
  • Run: SELECT physical_device_name, family_sequence_number, mirror FROM msdb.dbo.backupmediafamily WHERE media_set_id = (SELECT media_set_id FROM msdb.dbo.backupmediaset WHERE media_set_guid = 'YOUR-GUID-HERE');
  • Confirm the exact file paths and the total count of mirror devices expected (e.g., 2-way vs 3-way mirror).

Step 2: Correct the BACKUP Command Syntax

  • Rewrite the BACKUP DATABASE statement to include a MIRROR TO clause for every device, not just the primary disk target.
  • Example corrected syntax:
  BACKUP DATABASE [YourDB]
  TO DISK = 'E:\Backups\Mirror1\db.bak'
  MIRROR TO DISK = 'F:\Backups\Mirror2\db.bak'
  MIRROR TO DISK = 'G:\Backups\Mirror3\db.bak'
  WITH FORMAT, MEDIANAME = 'MirrorSet1';
  • Ensure the number of MIRROR TO clauses matches the original count found in Step 1.

Step 3: Verify File System and Path Accessibility

  • On Windows Server 2025 / Windows 11, confirm all drive letters or UNC paths referenced in the mirror clauses are online and accessible to the SQL Server service account.
  • Check via File Explorer or PowerShell: Test-Path "F:\Backups\Mirror2\"
  • If a physical drive was decommissioned or a network share was remapped, update the MIRROR TO path to the new valid location and restart the backup job.

Step 4: Rebuild the Media Set if a Mirror is Permanently Lost

  • If one of the original mirror devices is physically gone and cannot be recovered or recreated at the same path, you cannot append to the existing set.
  • Use WITH FORMAT to initialize a brand-new mirrored media set with the currently available devices rather than trying to append.
  • Update any SQL Agent Job steps or maintenance plans to reference this new media set GUID going forward.

Alternative or Verification Steps

  • After applying the fix, run the backup job manually in SQL Server Management Studio (SSMS) and check the Messages tab for a successful completion without Error 3240.
  • As a quick workaround, use WITH FORMAT, SKIP, INIT to force the creation of a fresh mirrored set if reconstructing the exact original mirror list is not feasible.