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

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

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

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

Log Shipping Concern

My department is thinking of implementing Log Shipping for a database. We
have a concern that, due to the nature of replication via log files, some
transactions may not execute properly on the secondary server and when this
occurs we will have essentially non-matching databases. Is this concern a
real issue and if so, how are such issues resolved?
Right now our situation allows us to dump and restore the database at any
time and take it offline if we have to. This dump and restore availability
ensures that we have two exact copies of the database with identical
footprints. Basically our concern is that we want to be assured an equal
concurrecy with log shipping.
Can anybody provide any insight?
Thanks,
Jeff
PS. I was not sure which group to post this in (I posted the same message
in this and in .Replication). Which group do Log Shipping questions belong
in?Log Shipping works but can be a pain in the neck.
the biggest problem we ran into was the occurence of "Non-Logged" Operations
in production.
this breaks log shipping quicker than you can blink.
Cheers,
Greg Jackson
Portland, OR|||Log shipping is mainly used for maintaining standby
Servers.
If you want to do logshipping for just only one database
in a server, make sure you are taking a right decision.
Compare the pros & cons of Replication and Log Shipping
and see whether log shipping is your right choice over
Replication. If you are more concerned about data
concurrency, check whether Replication solves your purpose.
Once log shipping has been implemented, it is relatively
easy to maintain. If Logshipping is implemented properly,
usually there wont be much issues.Its very robust.
I have encountered only the issues which is explained in
the articles below.
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/reskit/sql2000/part4/c1361.asp
This link may help you get better idea of Log shipping.
This is a webcast.
http://support.microsoft.com/default.aspx?kbid=821786
The issue you have mentioned about the non-matching
databases, I have not come across in my vast experience in
Logshipping or Replication. I think that,if the databases
on publisher and subscriber are in sync with every aspects
(contraints,data..), then this will not occur.
If I have understand correctly, then Replication is the
best and suits your purpose.
Hope this gives you some idea to start with.
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>My department is thinking of implementing Log Shipping
for a database. We
>have a concern that, due to the nature of replication via
log files, some
>transactions may not execute properly on the secondary
server and when this
>occurs we will have essentially non-matching databases.
Is this concern a
>real issue and if so, how are such issues resolved?
>Right now our situation allows us to dump and restore the
database at any
>time and take it offline if we have to. This dump and
restore availability
>ensures that we have two exact copies of the database
with identical
>footprints. Basically our concern is that we want to be
assured an equal
>concurrecy with log shipping.
>Can anybody provide any insight?
>Thanks,
>Jeff
>PS. I was not sure which group to post this in (I posted
the same message
>in this and in .Replication). Which group do Log
Shipping questions belong
>in?
>
>.
>|||>the biggest problem we ran into was the occurence of "Non-
Logged" Operations
>in production.
>this breaks log shipping quicker than you can blink.
If you are in a production environment, you shouldn't be
doing non-logged operations. And under SQL 2K, this
really for the most part isn't possible. You use Full or
Bulk-Logged, and log shipping works. Simple prevents you
from making tran log backups at all.
The non-logged issue was more of a problem in SQL 7 and
earlier.