3

As per below thread from SQL Server: Can I log ship from SQL Server 2014 --> 2016?

Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database
upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?

6

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server. The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only. This is documented in RESTORE Statements - Arguments (Transact-SQL):

STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead. You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker. But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database. It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

  1. On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.