Log Shipping version upgrade oddity

  • Hi,

    I've come across an odd situation, and was wondering if anyone can explain what is happening.

    I logship from a 2014 server to a 2014 server, putting the database into standby for reporting during the day.

    I'm migrating both to new 2019 servers. Initially I have the new secondary server restoring logs from the current 2014 server. As you would expect, you can't put this database into standby.

    Then I migrate the source to 2019, and I am now restoring the log backups from the 2019 source server to my secondary.

    If I restore the log backup from the new 2019 server WITH NORECOVERY, then do a "RESTORE DATABASE <name> WITH STANDBY...", it fails, telling me it needs to be upgraded.

    This is exactly what I would expect to happen.

    However, if I do the RESTORE LOG  and use the STANDBY option on the log restore, this works and goes into standby mode.

    Subsequent log restores then allow me to use the RESTORE DATABASE...WITH STANDBY with no issue.

    So, what I'm unsure of is why doing STANDBY on the log restore allows the upgrade to occur, but doing the log restore and then performing the STANDBY fails?  I would have thought these would be doing exactly the same thing?

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • My understanding is that when you restore in standby, it will do the crash recovery for the database and execute the Redo phase and store the Undo phase in the standby file.  This upgrade stuff is done in the LOG not in the Database restore of this kind.  So when you did the restore in the LOG with standby, then the restore database with standby it was able to do the crash recovery into the undo file and the log initiated the standby/upgrade. When doing the restore database after, it was already done, but should not allow you to do that if you restore the LOG with NORECOVERY again.

    I see this as by design behavior.

    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply