Log Shipping from SQL Server 2000 to SQL Server 2005

I work for a small state government department. We are about to install SQL Server 2005.

A national government department that runs on SQL Server 2000 are willing to give us a backup of their database (20 gig I think) and then log ship to us regulary. Our aim is to have a copy of the database that we can query from and that is only a day or so out-of-date.

I'm having no luck.

1. I can backup the SQL Server 2000 database.

2. I can restore the SQL Server 2000 database onto the SQL Server 2005 box no worries.

2. BUT, if I attempt to restore the 2000 database with the option 'Leave the database in read-only mode ...' selected (so that I can then restore transaction logs and it's read-only), then I get an error message. ""This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.". I'm doing all this using SQL Server Management Studio.

It appears we will have to use SQL Server 2000 rather than 2005 to get this working.

Q: Has anyone successfully done this from SQL 2000 to SQL 2005 please? If so, how was it achieved please?

Thanks.

Geoff - Did you find an answer to this?

I'm also trying to log ship from 2000 to 2005

|||

There is a good explanation about logshipping in the BOL.

"Upgrading a SQL Server 2000 Log Shipping Configuration"

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

The bottom line is that both ends of a log shipping configuration need to be at the same version.

In this case, you have a SQL 2005 instance on your end, and no ability to upgrade the primary database, as you don't control it, right?

One solution would be to install a SQL 2000 instance on your end, and use that for your end of the log-shipping configuration. There is no problem with having SQL 2005 and SQL 2000 instances side-by-side on the same machine.

You can then access the data in the SQL 2000 instance either from within that instance, or by setting up a distributed query from the SQL 2005 instance.

|||

hi,

I,m facing the same problem.

I know this case is already got asnwered status.

but I can't really use 2 sql, 2000 and 2005 cause it's license matter. I read that log shipping is consist 3 step.

1. back up the db

2. copy the backup

3. restore the db

I have lil experience in database. can it be done manually ?

anybody can help me create that step manually ?

regards,

-dedys

|||

Is this the official MircoSoft story on Logshipping between 2000 to 2005 SQL Servers?

I work at a company that could use SQL 2000 and SQL 2005 side by side on the Log Ship receiver

but I prefer to just restore the SQL 2000 transaction log back on the new SQL 2005 DB. Are the file

formats of the transaction log different between SQL 2000 and 2005? Thanks.

|||

Ok Chaps,

You cannot log ship from SQL 2000 to SQL 2005.

What you can do is.

1. Log ship from SQL 2000 to SQL 2000

2. Then upgrade the DR server to 2005.

3. And carry on log shipping until you recover or failover to DR.

The DR server database will remain in SQL 2000 until you recover it. This works if you carry on restoring the log with norecovery. Because you use standby, the recovery is performed each time, the above will not work for you.

Geoff,

For your case try using replication instead.

Jag