SQL 2000 to 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL 2000 to 2005

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-08-15 : 16:45:20

Hi all,

is it possible to do logshipping from SQL 2000 to SQL 2005?

and also

is it possible to do Data Replication from SQL 2000 to SQL 2005?

or do i have to have same standard on both server?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-15 : 16:50:50

You can do replication between 2000 and 2005 but not log shipping:
http://blogs.msdn.com/sanchan/archive/2006/02/12/530651.aspx

You could do custom log shipping from 2000 to 2005 though. Custom log shipping is when you write the scripts that do the backups, copies, and restores rather than using the built-in method. I wouldn't recommend doing it though.

What exactly do you need to achieve?

Tara Kizer

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-08-15 : 17:17:32

yeah i would not but i have done that in SQL std edition since it does not have Log shipping built in it. at that time i had to script everything to do the log shipping.

Thanks

wei_smooth
Starting Member

5 Posts

Posted - 2006-09-01 : 02:36:57

hi guys,

I have tries do the same thing. manually log shipping that is.
could you help me up. please share your experience.

because I'm having trouble with restoring the transaction log.
it seem that I can't restore it after the database is online.
while it still restoring status I can restore as many as transaction log that I want.

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-01 : 11:46:44

Of course you can't continue to apply transaction logs after the database is online. That's just not how it works. You could use the standby option between each restore. This will put the database in read-only mode.

Tara Kizer

wei_smooth
Starting Member

5 Posts

Posted - 2006-09-03 : 22:43:09

hi tara,

I have try to restore my full backup database using with standby option but it generated an error that said : "system.data.sqlclient.sqlerror: this backup cannot be restored using with standby because a database upgrade is needed. reissue the restore without with standby"

my source backup db is from SQL 2000. maybe thats why it need to be upgraded.

-dedys

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-04 : 02:32:16

quote:
Originally posted by wei_smooth

hi tara,

I have try to restore my full backup database using with standby option but it generated an error that said : "system.data.sqlclient.sqlerror: this backup cannot be restored using with standby because a database upgrade is needed. reissue the restore without with standby"

my source backup db is from SQL 2000. maybe thats why it need to be upgraded.

what happens when you try the NORECOVERY option instead of the STANDBY option?

-ec

wei_smooth
Starting Member

5 Posts

Posted - 2006-09-04 : 03:30:04

hi,

I already try with norecovery option.
when I resore my full backup and the transaction log. it went ok.
but we have to close with recovery option in order to use the db.

the problem is when I use with recovery option.
I can't put more transaction log.

it's seem that log shipping manually can't be done. can it ?

I read that we can do merge replication to achive the same result.
the publisher use SQL 2000 and the distributor and subscriber using SQL 2005. is it possible this way ? any body have try it ?

I'm open to any suggestion.

regards,
-dedys

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-04 : 20:27:23

That's how restores work. Once you specify the WITH RECOVERY option, you can't apply additional files. It has nothing to do with 2000 to 2005. It's just how restores work.

Why do you need to run WITH RECOVERY option to use the database? STANDBY option provides read-only access to the database plus the ability to apply additional logs.

Tara Kizer

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-04 : 22:28:52

quote:
Originally posted by tkizer

That's how restores work. Once you specify the WITH RECOVERY option, you can't apply additional files. It has nothing to do with 2000 to 2005. It's just how restores work.

Why do you need to run WITH RECOVERY option to use the database? STANDBY option provides read-only access to the database plus the ability to apply additional logs.

The STANDBY option seems to trigger some requirement to upgrade the database. That is why I suggested the NORECOVERY option.

I wonder what happens if you run the remote database as a version 8 database if that would still trigger that upgrade requirement on the STANDBY option.

-ec

wei_smooth
Starting Member

5 Posts

Posted - 2006-09-04 : 23:13:02

hi tara,

we can use with standby option because the db need to be upgraded first in order to be use by SQL 2005. so we can use with standby option.

I just get this link http://msdn2.microsoft.com/en-us/library/ms143241.aspx
but I still don't get it to make it work

please advice

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-05 : 00:06:08

SQL Server will upgrade the database for you during the restore into the 2005 server. You don't need to upgrade it first.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources