Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Fix a Point-in-Time Restore That Silently Fails Because an Earlier Switch to the Simple Recovery Model Broke the Transaction Log Chain

- 21.10.26 - ErcanOPAK

🗄️ 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.

— Database Engineer

Related posts:

SQL Server: Fix a Logon Trigger That Silently Blocks Every New Connection After a Typo

SQL Server: Fix a Logon Trigger That Silently Blocks Every New Connection After a Typo

SQL Server: Fix a Query Slowdown That Only Appears After the Table Crosses a Row-Count Threshold

SQL Server: Fix a Query Slowdown That Only Appears After the Table Crosses a Row-Count Threshold

SQL Window Functions: Analytics Queries That Replace Hundreds of Lines of Code

SQL Window Functions: Analytics Queries That Replace Hundreds of Lines of Code

Views: 14

Post navigation

C#: Fix a Regex Marked RegexOptions.Compiled That Silently Makes Startup Slower Without Making Matches Any Faster at Your Actual Call Volume
JavaScript: Fix an Intl.NumberFormat Call That Silently Rounds Differently Than toFixed() for the Same Number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (981)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (981)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com