0

I have SQL2014 version database on my Windows12 server. I want to add the databases in SQL2014 to SQL 2022 on my Windows2016 server using the Log Shipping method. Since the versions are different, I think I had to select No Recovery Mode instead of Standby Mode in the "Restore Transaction Log" section. I also selected No recovery mode, but I could not access the database added from SQL 2022 on my Windows 2016 server. How can I access SQL2014 databases from SQL2022 database with Log Shipping?

Improve this question
3
  • Put it in STANDBY instead of NORECOVERY
    – Charlieface
    Commented Feb 6 at 15:28
  • dba.stackexchange.com/questions/55072/… In this link it says that WITH NORECOVERY should be used. Because my SQL versions are not the same. @Charlieface
    – Merve
    Commented Feb 6 at 15:49
  • 1
    Well there is your answer, it can't be done.
    – Charlieface
    Commented Feb 6 at 15:52

2 Answers 2

Reset to default
1

If you want to ship logs to a higher version and have them readable, you can do this by having multiple instances (or multiple copies of the database), and rotating through them. You simply move the cheese - e.g. always point the next reader (though app config, or DNS, or synonyms, or views, or many other mechanisms) at the "current" copy of the tables.

  1. Restore with recovery to copy A. Point readers at copy A.
  2. Restore with recovery to copy B. Point readers at copy B.
  3. ... keep doing that until you get to as many copies as you need ...
  4. Kick any straggling users out of copy A. Go to step 1.

I walk through this in much more detail here:

Feels like you'd be better off just upgrading the source, though. Why are you holding onto SQL Server 2014 and Windows Server 2012? Updating the source to match would make this scenario easier. But if you are on Enterprise Edition it would also allow for a much more optimal solution - put both servers in the same cluster, set up an availability group, and use one node as read-only secondary.

Improve this answer
0

If you are log shipping to a newer version of SQL Server there would be no way to read from the databases. The STANDBY option won't work. You would need to restore with RECOVERY to read from them - which will prevent any further log restores.

Usually log shipping is done to a newer version of SQL Server as part of a migration process to minimize downtime. If you are doing it for HA/DR purposes, log shipping to a newer version is not recommended as you wouldn't be able to go back to the old primary (Not without upgrading it to the newer version). If you just want a copy to read from - keeping the versions the same is mandatory.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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