Help: SQL Server

“All characters appearing in this blog are fictitious. Any resemblance to real persons, living or dead, is purely coincidental… except me”

<Tring…Tring>
Peter: Hello Balmukund
Me: Hey Peter. How are you?
Peter: Not so good. Having some trouble with a critical server.
Me: Yeah, I knew that you would call me when there is an issue with critical server. <laugh> Go ahead and explain me the issue.
Peter: I am not able to restore the transaction log backup and getting error “Unable to create a restore plan due to break in the LSN chain”

Me: Wow. Never heard of “restore plan” error. hang on a second.. let me open my laptop.
Peter: Sure.
Me: Okay. What was the error again? unable.. restore plan..?
Peter: Unable to create a restore plan due to break in the LSN chain.
Me: Well, interestingly, I don’t see that error in sys.messages table in SQL Server Instance.
Peter: but it coming in SQL Server.
Me: Where exactly you are seeing this error? Tell me exact steps.
Peter: I am seeing that error in Management Studio. I am using SQL Server 2012 Instance and SQL Server 2012 management studio to restore backups. I have restored one full backup and now want to restore next log backup.
Me: And there is no real break in the chain?
Peter: Yes. I have taken them manually, one after another.
Me: Okay. Would you mind sending me screenshot and complete error via email. I am on my laptop right now. Let’s fix it right away.
Peter: Sure. <1 min silence> Sent.
Me. Okay. Let me received it and meantime I am searching. <30 second silence>  Okay. Got the email.

Unable to create restore plan due to break in the LSN chain. (Microsoft.SqlServer.SmoExtended)
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.3000.0+((SQL11_PCU_Main).121019-1325+)&LinkId=20476
——————————
Program Location:
   at Microsoft.SqlServer.Management.Smo.DatabaseRestorePlanner.SelectBackupSetsForPlan(RestorePlan plan)
   at Microsoft.SqlServer.Management.Smo.DatabaseRestorePlanner.CreateRestorePlan(RestoreOptions ro)
   at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseDialogController.<>c__DisplayClass2.<CreateRestorePlan>b__1(IBackgroundOperationContext backgroundContext)

Me: Okay. that’s SMO error and that’s why I don’t see it in sys.messages. It is failing while creating restore plan to show in UI.
Peter: Aha. So, what should I do.
Me: T-SQL would work for sure. can you use “Restore log from disk = ‘Path’ command?
Peter: Ahem.. any other way.. You know how much T-SQL.
Me: Let me see. <1 min silence> I believe you have used “restore database” UI? Correct?
Peter: Yes.
Me: Okay. I can see that this is an issue filed in SQL 2012 management studio. (reference)
Peter: Is it fixed?
Me: Yeah, fixed in SQL 2014 Management studio already.
Peter: So what should I do?
Me: You have many options now.
1) Use SQL 2014 SSMS
2) Use “restore files and filegroups”

3) Use different restore option in UI

4) Drop the database and restore both backups together (full and log) in same UI. Haven’t you heard of recovery advisor? I will send you link later.
5) and last option, which you won’t like is T-SQL.

Peter: Wow. So many solutions to one simple problem. You are a Gem!
Me: You are so kind. Thanks.
Peter: Okay. Let me do restore now. My manager is pinging me to fix this ASAP.
Me: Sure. Talk to you later. Bye.
Peter: Bye

Like the story? Make a comment.