Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for May, 2014

Help : Getting error “Unable to create a restore plan due to break in the LSN chain”

Posted by blakhani on May 29, 2014


“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.

image

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”

image

3) Use different restore option in UI

image

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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    Posted in SQL Server | Tagged: , , , , | Leave a Comment »

    Solution: The file "MDF" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

    Posted by blakhani on May 22, 2014


    Recently I was trying to create a database on my SQL Instance using below command

    CREATE DATABASE [MyDatabase]
    ON   
    ( NAME = N'MyDatabase', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase.mdf')
    LOG ON 
    ( NAME = N'MyDatabase_log', FILENAME = N'E:\BlogContent\DatabaseFiles\MyDatabase_log.ldf')
    GO

    But encountered below error

    Msg 5118, Level 16, State 1, Line 1
    The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.
    Msg 1802, Level 16, State 4, Line 1
    CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

    Error giving hint about some kind of compression. So if we look at the folder which is specified E:\BlogContent\DatabaseFiles it was in blue color.

    image

    Blue color actually indicates that folder is compressed. Right Click on folder, go to “Properties” and click on “Advanced…”

    image

    The checkbox “Compress contents to save disk space” is checked which means compression is enabled. Once we uncheck the box, we should be able to create database.

    Here is official stand about compression http://support.microsoft.com/kb/231347/en-us (SQL Server databases are not supported on compressed volumes). There is a great blog by Ken Henderson on this topic detailing the ramifications and I would encourage you to read it http://blogs.msdn.com/khen1234/archive/2005/04/25/411852.aspx 

    If we try to restore a database and keep files on compressed folder, same error is raised (in SQL 2014 Management Studio)

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    Restore of database 'MyDatabase' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
    ------------------------------
    ADDITIONAL INFORMATION:
    System.Data.SqlClient.SqlError: The file "E:\BlogContent\DatabaseFiles\MyDatabase.mdf" is compressed but does not reside in a read-only database or filegroup. 
    The file must be decompressed. (Microsoft.SqlServer.SmoExtended)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------

    Hope this helps!

     

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in Error | Tagged: , , | 18 Comments »