Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

    Advertisements
  • Blog Stats

    • 1,644,887 hits
  • Select GETDATE()

    July 2019
    M T W T F S S
    « Apr    
    1234567
    891011121314
    15161718192021
    22232425262728
    293031  
  • Advertisements

Archive for the ‘Logshipping’ Category

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 AlwaysOnPaperback, Kindle
  • Advertisements

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

    Log Shipping Message : Skipping log backup file since load delay period has not expired

    Posted by blakhani on August 21, 2014


    During my last visit to one of my friend’s home I was surprised to hear that he had a problem in log shipping where he wanted my help. It was a family meet-up and I never thought it would become technical. Anyways, I asked the problem and he mentioned that he has configured log-shipping for one database between two servers. He told me that he was getting some “delay” error and I asked to give the exact error message. Below is what we saw in “Restore Job history”

    2014-06-21 06:14:30.45     Starting transaction log restore. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
    2014-06-21 06:14:30.45     Retrieving restore settings. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
    2014-06-21 06:14:30.45     Retrieved common restore settings. Primary Server: ‘SQLPAPA’, Primary Database: ‘LogShipDemo’, Backup Destination Directory: ‘E:\LS\Secondary’, File Retention Period: 4320 minute(s)
    2014-06-21 06:14:30.45     Retrieved database restore settings. Secondary Database: ‘SomeName’, Restore Delay: 2, Restore All: True, Restore Mode: No Recovery, Disconnect Users: False, Last Restored File: E:\LS\Secondary\SomeName_20140621004200.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
    2014-06-21 06:14:30.51     Skipping log backup file since load delay period has not expired. Secondary DB: ‘SomeName’, File: ‘E:\LS\Secondary\SomeName_20140621004231.trn’
    2014-06-21 06:14:30.51     The restore operation was successful. Secondary Database: ‘SomeName’, Number of log backup files restored: 0
    2014-06-21 06:14:30.51     Deleting old log backup files. Primary Database: ‘SomeName’
    2014-06-21 06:14:30.51     The restore operation was successful. Secondary ID: ‘8a3c7cae-6df2-4c6e-b528-2062d4b830f0’
    2014-06-21 06:14:30.51     —– END OF TRANSACTION LOG RESTORE     —–

    So the question he asked were:

    • What is this "Load delay period" and what’s the meaning of the error?
    • Can we configure this somehow, somewhere?

    Answer

    • Load delay is a period what is given to secondary server to wait to restore the backup file. We can use this if we want to wait upon the copy job.
    • We can set this value in the SSMS
      Database > Right click > properties -> Transaction Log shipping -> Secondary databases -> Restore Transaction Log

    image

    If we want to check via T-SQL then we can see that in MSDB database of secondary server.

    Select  secondary_database, 
            restore_delay 
    from    msdb.dbo.log_shipping_secondary_databases

    If the load delay is set to zero and still we are seeing a long delay in restore job picking the file to restore then next thing to check would be if there is a time difference between primary and secondary servers. Having different time zone is not a problem but but UTC time should match else we would see such problems.

    Hope this helps!

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