SQL Server / Upgrading the Secondary Server Instances (STANDBY)

If you planning to upgrade to the new SQL Server Version 2016 or 2017 and if there is log shipping in place between those servers you should follow some upgrading order.

Always upgrade the secondary SQL Server instances first, before upgrading the primary server instance. If you do the opposite log shipping will fail because a log backup created on a newer version of SQL Server cannot be restored on an older version of SQL Server.

But this is not a reason why I am writing this post. I am writing to alert you that in case you have a secondary database configured by using RESTORE WITH STANDBY, transaction logs restore will fails after the upgrade.

Instead this state

You will have

And you will see an error message in your error log file.

Recovery for database ‘DBname’ is being skipped because it requires an upgrade but is marked for Standby. Use RESTORE DATABASE WITH NORECOVERY to take the database back to a Restoring state and continue the restore sequence.

If you are satisfied with secondary databases in restoring state there is a solution for this problem.

First solution:

You can put your databases in restoring state before you do the upgrade.

LS process will continue to restore the log backups from SQL Server primary server instance without any issue. However, databases in restoring state on secondary server will not be automatically upgraded. To achieve this you need to do restore with recovery and put them online.

But keep in mind that once you recovered the database, the primary and the secondary diverged. Due to, you need to redo whole log shipping process again from start, restore backup again and re-establish log shipping

Second solution:

After upgrade is done you can put all your standby databases in restoring mode (with norecovery), and change existing log shipping process from standby

to restoring

Nevertheless, if you are using this server as a reporting server, unfortunately, this is not the solution for you. I did not find solution for using standby mode on the LS secondary server if the secondary is newer SQL version.

If you still keep your standby settings in LS configuration you will get an error

Error: This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider)

Useful links

Upgrading Log Shipping to SQL Server 2016 (Transact-SQL)

Similar Posts:

• SQL Server / Initialize Secondary Database / Generate a full backup of the primary database using SSMS transaction log shipping GUI issue
• Log Shipping Issue / Operating system error 32 (The process cannot access the file because it is being used by another process)
• SQL Server / SQL Server Management Studio could not delete the Secondary server / Secondary not available
• PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs
• SQL Server / Backup File Size Issue