Log Shipping between SQL 2005 and SQL 2000

We have an environment where 3 SQL 2000 servers exist; one production servers and two standby servers that receive logs via log shipping. Everything works fine and it is stable. My client wants to get a new 64 bit server and run SQL 2005 64 bit version for the production box. Can we still do log shipping with the two remaining SQL 2000 boxes or do they all have to be on the same version and bit version?

Thanks

Bill

moving to HA forum.|||

You can ship from 2000 to 2005 (uplevel) but not from 2005 to 2000 (downlevel) because the log format is slightly different in 2005.

Thanks

|||

Hey Paul,

When applying a transaction log built from a SQL2000 (version 80) instance to SQL 2005 instance but the database is in a (version 80) configuration I still get the following error:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Though if I were to restore the FULL backup to the SQL 2005 instance in with RECOVEY option I can then create a backup of it then restore it in STANDBY mode ... issue here is now I can apply any transaction logs, I then get this error

This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Any thoughts as to how to apply a 2000 transaction log to a 2005 instance and leave the database in STANDBY mode?

Thanks
Eric

|||

Unfortunately, although you can maintain log shipping from SQL 2000 to a SQL 2005 instance as long as the target database is never recovered and converted to 90 format, you cannot have the target database in standby mode.

Putting a database in standby mode implies that you are recovering the database and writing the uncommitted changes out to the standby file. The last stage of recovery when a 80-format database is on a SQL 2005 instance is to upgrade the format to 90. It just doesn't logically make sense to have a 90-format database with a standby file of 80-format changes.

As soon as the database recovers, it will be in 90 (SQL 2005) format. A database in 90 format cannot have 80 format logs applied to it because as Paul points out, the physical format is different.