One of the discussion lists that I participate in, had a brief discussion this morning about whether or not it's possible to perform log shipping between differernt versions of SQL Server. Specifically, can you do log shipping between SQL Server 2005 and SQL Server 2008?
SQL Server does support restoring earlier version databases on later versions of the product. The databases get upgraded along the way when you perform restores of databases. SQL Server also allows you to restore transactions logs from earlier versions of the product but (as Robert Davis points out in the comments below), the upgrade doesn't happen until recovery of the database occurs. And that's why you can't use STANDBY mode in this situation.
So, you can set up log shipping between versions, however things aren't that simple. Log shipping is often used to provide a warm standby. If you use it in this way and you need to fail over to the standby server, you now have now way to swap the log shipping roles, as you can't then log ship back from the 2008 server to the 2005 server.
If you are performing a one way log ship, intentionally, this might be quite acceptable to you. I often see log shipping used when servers are being upgraded from one version of SQL Server to another version, even side-by-side. When it's time for the swap to the new server to happen, the final logs just need to be moved and this takes very little time. There are other reasons as well as to why you might be happy to just have a one-way log shipping operation.
The main point is that you need to consider why you are performing log shipping before you do this. If it's with a view to swap roles from primary to secondary and back, then log shipping between versions isn't for you.
16 thoughts on “Log Shipping Between SQL Server Versions (perhaps 2005 to 2008)”
Actually, the databases don't get upgraded along the way. The upgrade doesn't occur until the database is recovered. For this reason, it is not possible to log ship up version in stand-by mode. You can do it if you leave the database offline (restoring mode). If you try to use stand-by mode, you'll get an error message stating that the backup is from a different version of SQL Server becuase it can't run crash recovery until the database is upgraded.
Thanks Robert. On re-reading that, I see the confusion. My sentence about it being supported for logs was intended to relate to the ability to restore, not to some sort of ongoing upgrades. So, I've (hopefully) clarified it.
We are in the Process of Upgrade from SQL Server 2005 to SQL Server 2008 R2.We want to keep SQL Server 2005 Server and SQL Server 2008 R2 Server Running. Use Log Shipping From SQL Server 2005 to SQL Server 2008 R2. This will enable the Dev team to conect to the New SQL Server 2008 R2 Server and Test every thing before go live in SQL Server 2008 R2. To keep both Server sync, we want to do Log Shipping from SQL Server 2005 to SQL Server 2008 R2. Could we have the Secondary (SQL Server 2208 R2) Databases on STANDBY Mode. Then the Dev Team can connect and test. Once we go Live with SQ Server 2008 R2, could we do log Shipping From SQL Server 2008 R2 to SQL Server 2005. May in STANDBY Mode.
No, as Robert pointed out in his comment, you can't use standby in that situation.
Thanks for your article.
What if we don't swap roles from primary to secondary and back?
I want to do fail over just once.
That works fine and is what we do for upgrades quite often
But when I want to configure log shipping I get this error:
This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.
I can not set the database in standby mode.
I want to upgrade from SQLServer 2005 to 2008R2.
You can log ship but you cannot do STANDBY. Using STANDBY would require the database to be upgraded which it isn't until recovery is run.
tested logshipping from 2005 to 2014, it works as well, however you need to initialize database on 2014 first, it doesn't work from wizard
I had made log shipping with two instances. I deleted the secondary database in second instance. Now i tried to create the log shipping again from the primary instance and with same database. When i click the transaction log shipping in the primary database properties, It is giving an error message that the current log shipping configuration is not functional because the required backup job cannot be found. My question is how you do delete the log shipping that has already been established between two instances and recreated it for the same data bases.
If you've manually disabled part of the configuration, unfortunately you'll need to manually delete any other part of the configuration that remains. In MSDN, there is a list of the log-shipping-related tables. Make sure that none of those return data. Also make sure you have removed any log-shipping-related jobs.
According to me what Robert has pointed out in this post is Inaccurate .Standby Option is possible from SQL 2005 to SQL 2008 if while configuring logshipping you choose the database has been initialized script upgrade mode has sucessufully completed on the database if it has been restored successfully and it should show a compatibility level of 100.on the other hand if you Automate this process through SSMS it is not possible to keep stanby mode.
No Robert is correct. You can't enable STANDBY when the target database server is a later SQL Server version.
you can use standby if you have initialized the database I have tested it Greg . In logshipping through GUI when you choose the database is initialized that means you perform the backup and restore and initialize the database first and the database goes through a script upgrade mode already as there is a change in compatibility level it changes to 100 from 90 if the backup is of sql 2005 . on the other hand if you Ask SSMS to initilaize the database for you the standby mode can not be used because even if the database is in standby on secondary server its online script upgrade can not run on a database which is online its like using a trace flag -T902 to skip the Script upgrade so that is why Standby mode cannot be used in this situation .
So to keep things short would like to say it can be used when you initialize the database first and cannot be used if you ask the the GUI to initialize the database when configuring logshipping .
Thanks , Hope it may help your bloggers 🙂
Hi Can we log ship between sql version 2005 to sql version 2008
Yes, you can log ship from 2005 to 2008 but you can't:
* Do standby mode
* Ever log ship in the other direction