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

by

Why This Error Happens

Error 1407 occurs when SQL Server attempts to establish or resume database mirroring or an Always On Availability Group relationship, but the LSN (Log Sequence Number) chain or database GUID on the secondary/remote copy does not match the primary database’s lineage. This typically happens when the secondary database was restored from a different backup chain, the database ID/recovery fork GUID was broken by an intermediate restore, or the mirroring/AG metadata became desynchronized due to a failed failover. It can also surface after a database was dropped and recreated locally without re-establishing the mirroring partnership properly.

Step-by-Step Resolution Guide

Step 1: Verify the Backup Lineage and Recovery Fork

  • Run RESTORE HEADERONLY against the last full and log backups to confirm the DatabaseBackupLSN and FirstLSN values align between primary and secondary.
  • Check sys.database_recovery_status and compare last_log_backup_lsn on both instances using: SELECT database_id, last_log_backup_lsn FROM sys.database_recovery_status;
  • Confirm no out-of-band restores (e.g., a manual restore from an older backup set) broke the chain.

Step 2: Remove the Broken Mirroring/AG Partnership

  • On the primary, run: ALTER DATABASE [YourDB] SET PARTNER OFF; (for legacy mirroring) or remove the database from the Availability Group via ALTER AVAILABILITY GROUP [AGName] REMOVE DATABASE [YourDB];
  • On the secondary instance, drop the orphaned database copy: DROP DATABASE [YourDB];
  • Clear any stale endpoint sessions in SQL Server Configuration Manager if mirroring sessions remain listed as “Suspended.”

Step 3: Re-Seed the Secondary Database from a Fresh Full Backup

  • Take a new full backup on the primary: BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB_Full.bak';
  • Restore it on the secondary using WITH NORECOVERY: RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB_Full.bak' WITH NORECOVERY;
  • Apply any subsequent transaction log backups in sequence, also WITH NORECOVERY, ensuring no gaps in the LSN chain.

Step 4: Re-Establish the Partnership or Add Back to the Availability Group

  • For mirroring, re-run the ALTER DATABASE [YourDB] SET PARTNER = 'TCP://SecondaryServer:5022'; command on the primary.
  • For Always On AG, use ALTER AVAILABILITY GROUP [AGName] ADD DATABASE [YourDB]; on the primary after the secondary is in a restoring state.
  • Confirm firewall rules on Windows Server 2025 allow the mirroring endpoint port (default 5022) through New-NetFirewallRule -DisplayName "SQL Mirroring" -Direction Inbound -LocalPort 5022 -Protocol TCP -Action Allow.

Alternative or Verification Steps

  • Check the “Always On Availability Groups” dashboard in SSMS or query sys.dm_hadr_database_replica_states to confirm synchronization_state returns SYNCHRONIZED or SYNCHRONIZING with no errors.
  • If re-seeding fails repeatedly, use the Automatic Seeding feature (ALTER AVAILABILITY GROUP ... MODIFY REPLICA ... WITH (SEEDING_MODE = AUTOMATIC)) to let SQL Server handle the full data transfer and avoid manual backup/restore mismatches.