A client was preparing to move databases from on-prem to Azure SQL Managed Instance. The backup and restore process was going smoothly, full backup restored, differential restored, first log backup restored, everything lining up. Then the next transaction log restore failed due to a mismatched LSN error. The backup chain was broken. The migration was stalled.
The Investigation
The error message from Azure Managed Instance told the story right away: “The restore plan is broken because firstLsn (12345000000456700001) of log backup XXXXX is not equal to the lastLsn (12345000000123400001) restore point in time failed.” If you’ve worked with SQL Server backups, you know what the LSN (Log Sequence Number) is. It’s how SQL Server knows where the last backup left off and where to start the next one, so every transaction is captured. When those numbers don’t line up, SQL Server won’t let you restore, and for good reason.
My client had a process in place since they were running the migration, and we were there to catch them if needed. They were going to stop backups with their third-party backup tool, then use native SQL Server backups pointed to Azure Blob Storage to build their restore chain into Managed Instance. The plan made sense.
I quickly grabbed our trusty community tool sp_CheckBackup and ran this on the source:
SP_CheckBackup @Mode = 3, @DatabaseName = 'TheDBTHatIsErroringEvenWithTHeirPlan'Mode 3 checks for split backup chains, backups going to different locations, anything that could complicate recovery. Even though they had that plan. this sure felt like a split backup chain… My gut was right. Some log backups were taken outside of the Blob Storage backup plan, after the initial full, differential, and logs. “Rogue Backups,” we call them at Straight Path (though sometimes it gets typoed to rouge backups – so we’ve started calling them by either name.)
Here’s what the timeline looked like:
- 12:00 PM – Full backup to Blob Storage (restored successfully)
- 6:00 PM – Differential backup to Blob Storage (restored successfully)
- 6:10 PM – Transaction log backup to Blob Storage (restored successfully)
- 6:20 PM – Rogue transaction log backup taken by the third-party backup tool (not in Blob Storage)
- 7:10 PM – Next scheduled log backup to Blob Storage (FAILED to restore, LSN mismatch)
That rogue backup at 6:20 PM was the problem. Even though the client had removed the database from their third-party backup policy, the backups didn’t actually stop running. That third-party log backup broke the chain that had been established in Blob Storage because it advanced the LSN past what our next Blob-based log backup expected.
The Fix
Stop the third-party backup solution entirely before initiating backups to Blob Storage. Don’t just remove the database from the policy. Actually confirm the backups have stopped. Run our free community tool, sp_CheckBackup to verify no rogue backups are sneaking in before you start your restore chain.
The Lesson
In this case, my client took a fresh full and differential during the migration activity and was eventually successful, but it was painful. And this was for a 1-2GB database. Imagine this with your 2TB or even 24TB database at GO LIVE. Quivers in chair.
Here’s what you should be doing if you’re planning a migration to Managed Instance (or any backup/restore migration):
- Confirm all third-party backup tools are fully stopped, not just “removed from the policy.” Verify they are no longer touching the database. Run sp_CheckBackup to confirm.
- Validate your backup chain before you start restoring. Use sp_CheckBackup @Mode = 3 to check for split chains and unexpected backup sources. If someone or something else took a backup you weren’t expecting, you want to know before you’re mid-migration, not after.
- Bring your DBA team into migration planning early. With a little coordination on a planning call, gotchas like broken backup chains can be spotted before the big day. This one could have been solved immediately if we’d been on the call when it happened. And during cutover planning, we could have flagged the risk before it became a problem with a bit more involvement from the start.
Rogue backups are one of the most common issues we see across new client environments. Multiple tools taking backups of the same database, often without anyone realizing the other exists. Mike Walsh wrote about this exact pattern in SQL Server Backups and the Illusion of Safety, where he points out that when everyone is responsible for backups, sometimes no one is actually accountable for recoverability.
The Straight Path Team and Skills
Backup chain integrity is one of the first things we train any new DBA on at Straight Path. sp_CheckBackup is one of our free community tools, largely built by Jeff Iannucci and David Seis. I’m confident that anyone on our team would have done the same troubleshooting here. Backups and recoverability are DBA skills 101, and the majority of our clients perform routine restore testing and integrity checks to make sure data is safe and ready for when it matters most.
Straight Path Solutions is a team of nearly 20 SQL Server experts trusted by 120+ clients. We treat our clients’ environments as if they were our own because our job depends on your success. Making your life pain-free in the land of SQL is the rewarding part of our work. If you’re planning a migration, dealing with backup concerns, or just want to sleep better knowing someone’s watching your environment, reach out to us.
This post is part of our Case of the Week series, real SQL Server issues and lessons from the field.