🗄️ The Restore That Needed a Log Chain Nobody Kept Intact
Point-in-time restore depends on an unbroken chain of transaction log backups going back to the last full backup. Switching a database from full to simple recovery model, even briefly for what seemed like a harmless maintenance operation, truncates the transaction log and permanently breaks that chain at that exact point – so even after switching back to full recovery and resuming log backups normally, a restore can only reach a point in time after the switch, never anything in the gap, and SQL Server gives no warning at the moment of the switch that this capability was just lost.
🔎 The Problem
-- Someone runs this during a routine maintenance window, intending -- to temporarily reduce log growth during a large batch operation: ALTER DATABASE SalesDb SET RECOVERY SIMPLE; -- [large batch operation runs, log stays small as intended] -- Afterward, switched back as planned: ALTER DATABASE SalesDb SET RECOVERY FULL; -- Everything LOOKS fine. Log backups resume on schedule: BACKUP LOG SalesDb TO DISK = 'SalesDb_log_1.trn'; -- Succeeds without any error or warning. -- Weeks later, an incident requires restoring to a specific moment -- that falls BEFORE the maintenance window: RESTORE DATABASE SalesDb FROM DISK = 'SalesDb_full.bak' WITH NORECOVERY; RESTORE LOG SalesDb FROM DISK = 'SalesDb_log_1.trn' WITH STOPAT = '2026-09-14T03:15:00', NORECOVERY; -- Msg 4305: The log in this backup set begins at a point too recent -- to apply to the database. An earlier log backup that includes the -- needed range can be restored instead. -- The log backup taken right after switching back to FULL starts a -- BRAND NEW log chain from scratch - it cannot be chained to -- anything backed up before the switch to SIMPLE, no matter how -- carefully those earlier backups were kept.
✅ Fix: Take a Full Backup Immediately After Any Switch Back to FULL
- Treat switching to full recovery as the start of an entirely new, independent log chain, not a resumption of the old one – take a full backup immediately after switching back, and understand that point-in-time restore capability for anything before that switch is permanently gone, not just temporarily unavailable.
- If log growth during a specific operation is the real concern, address it directly instead of switching recovery models – pre-size the log file appropriately, run the batch operation in smaller committed chunks, or schedule more frequent log backups during the operation so the log can keep truncating normally without ever leaving full recovery at all.
- Add a scheduled check that flags any unplanned recovery model change immediately, since the actual damage, the broken log chain, happens silently at the exact moment of the switch, not at the much later moment someone tries to restore and discovers it.
🚨 Why This Is Dangerous
- Every single operation along the way reports success – the switch to simple recovery, the maintenance operation, the switch back to full, and every log backup taken afterward – so there’s no alert, warning, or failed job anywhere to notice; the only moment this becomes visible is during an actual restore, which is the worst possible time to discover a capability assumed to still exist.
- The gap this creates is retroactive and permanent – it’s not that recovery capability is reduced going forward, it’s that a specific window in the past, which may contain exactly the incident that now needs investigating, becomes permanently unrestorable to any point within it, with no way to fix it after the fact.
Switching back to full recovery doesn’t repair the log chain, it just starts a new one – and a point-in-time restore has no way to cross a gap in the chain, however briefly simple recovery was turned on.

