Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,039,819 hits

  • Select GETDATE()

    April 2023
    M T W T F S S
     12
    3456789
    10111213141516
    17181920212223
    24252627282930

Posts Tagged ‘error’

Restore ERROR : This backup cannot be restored using WITH STANDBY because a database upgrade is needed

Posted by blakhani on May 21, 2015


One of my colleague in office sent an email to find the cause of the error. He wanted to configure Log-Shipping with secondary on Standby mode. Rather than “reply to all”, I generally ping the person directly to tell the possible reason of error. Here is what we discussed (let’s assume that his name is Manoj – M)

B: Hi Manoj
M: Hello
B: Saw you email to DL about log shipping problem.
M: Oh yeah. Thanks for reaching out.
B: No problem.
B: Are you restoring database from lower version to higher version of SQL?
M: No, both are 2008.
B: That’s doesn’t sound right. Can you please run Select @@version command in Management Studio on both the server and share the output?
M: Sure. Give me a minute to connect and run the query.
B: Sure

<1 min pause>

M: There you go

Source:
Microsoft SQL Server 2008 (SP1) – 10.0.2573.0 (X64)
Feb 4 2011 11:27:06
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Destination:
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

 

B: Okay. that is the problem. You are taking backup from SQL 2008 and restoring on SQL 2008 R2 in standby mode.
M: Both are not 2008?
B: No, destination is 2008 “R2” which is the next release after 2008.
M: Oh.. I thought its like a service pack on top of SQL 2008.
B: Unfortunately, its not a service pack. It’s fresh release having some enhancements.
M: Okay. So error message is correct.
B: Yeah. If you want to use secondary for reporting then you have to use standby mode. This can only be done on destination which has same version of SQL. Or you need to upgrade current primary server.
M: Hmm.. I got it now. Let me check with my DBA team and management to decide the next action.
B: Sure Manoj. Is there any other clarification you might need?
M: No Balmukund. I am good at this point. You have been very helpful. Appreciate it.
B: My pleasure. Bye for now.
M: Bye!

In summary, Here is what you would get in SSMS if we try to restore a backup from lower version of SQL to higher version of SQL in standby mode.

TITLE: Microsoft SQL Server Management Studio
——————————
SQL Server Management Studio restore database ‘DabbaWala’
——————————
ADDITIONAL INFORMATION:
Restore failed for Server ‘Destination’.  (SqlManagerUI)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
——————————
System.Data.SqlClient.SqlError: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY. (Microsoft.SqlServer.Smo)
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
——————————

 

The error is raised because during restore from lower to higher version the database version needs an upgrade to match with system table (or any other) change in higher version. Here is the query to see the version I am talking about. 

Select name, version from sys.sysdatabases

This version upgrade is part of recovery process and we can’t pause recovery in middle and view the database state (that’s what is done in standby mode). So, the only way to restore that would be to use “with recovery” or “with norecovery” option.

Hope this helps!

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle
    • Advertisement

    Posted in Error, Logshipping, Restore, SQL Server | Tagged: , , | 2 Comments »

    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 AlwaysOn – Paperback, Kindle

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