THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Log Shipping Between SQL Server Versions (perhaps 2005 to 2008)

This blog has moved! You can find this content at the following new location:

Published Wednesday, January 12, 2011 11:08 AM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Robert L Davis said:

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.

January 11, 2011 11:36 PM

Greg Low said:

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.

January 12, 2011 12:10 AM

Satha Jeyaramn ( said:

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.

June 13, 2013 2:39 AM

Greg Low said:

Hi Satha,

No, as Robert pointed out in his comment, you can't use standby in that situation.



June 13, 2013 3:56 AM

jam Mah said:

Hi Greg

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.

July 8, 2014 12:30 AM

Greg Low said:

That works fine and is what we do for upgrades quite often

July 8, 2014 2:01 AM

jam Mah said:

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.

July 10, 2014 4:14 AM

Greg Low said:

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.

July 10, 2014 4:36 AM

Iurii said:

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

April 21, 2015 3:23 PM

Siddiqui said:

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.

July 6, 2015 4:52 PM

Greg Low said:

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.

July 13, 2015 6:27 AM

Ajay said:

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.

January 7, 2016 5:32 PM

Greg Low said:

No Robert is correct. You can't enable STANDBY when the target database server is a later SQL Server version.

January 7, 2016 6:53 PM

Ajay said:

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 :)

January 15, 2016 11:11 AM

Bhupendra Durgapal said:

Hi Can we log ship between sql version 2005 to sql version 2008

January 19, 2016 8:34 AM

Greg Low said:

Yes, you can log ship from 2005 to 2008 but you can't:

* Do standby mode

* Ever log ship in the other direction

January 19, 2016 4:31 PM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement