THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): 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)

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.

 

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

Comments

 

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 (Satha.Jeyaramn@amcom.com.au) 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.

Regards,

Greg

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement