x

Can I log ship from SQL Server 2000 to SQL Server 2005?

I have a SQL Server 2000 instance and want to provide some DR protection. There is a remote office with a SQL Server 2005 instance that is set up for log shipping from another SQL Server 2005 instance here.

Can I log ship my SQL Server 2000 databases to this remote SQL Server 2005 instance?

more ▼

asked Oct 21, 2009 at 02:38 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 77 79 82

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Log Shipping can be resumed as:

  • a backup at the main server
  • a copy of the data to the destination ( or shared network address ).
  • a restore at the "backup" server.

I Am not sure if 2005 integrated Log Shipping will work with 2000, mostly because of the schedulers and all monitoring stuff it requires.

But even if the integrated solution didn't work, you can perfectly create one manualy, and depending on your skills it could work better then the original one.

On small/medium DBs i prefer doing a full backup daily, but on large/very_large DBs you will need to rethink about it.

Basically you will need 2 jobs at each server.

Main Server:

  • Full backups at your favorite schedule followed by a backup log with init.
  • Log backups every 5-15 mins with noinit ( to append them to the same file ).

"Backup" server:

  • Full backup restore with 2 steps, copy DB locally and restore with no recovery
  • Log restore with 2 steps, copy Log file locally and restore with no recovery.

Well, if you could do it to another 2005 and use DB mirroring instead of Log Shipping, it would be a better DR protettion IMHO.

more ▼

answered Oct 21, 2009 at 02:53 PM

Gustavo gravatar image

Gustavo
592 4 4 7

(comments are locked)
10|1200 characters needed characters left

Although it would typically be a bad idea for reasons I'll list below, in the spirit of 'some protection is usually better than none'...

Because SQL Server 2000 backup files can be restored to a SQL Server 2005 instance, the answer is technically 'Yes', but you cannot implement it with the native SQL Server log shipping tools.

You would need to implement your own log shipping solution that takes transaction log backups on your SQL Server 2000 database then restores them on the SQL Server 2005 DR intance with NORECOVERY or STANDBY. If you choose use STANDBY, be sure to jealously protect your Transaction Undo File (.TUF) or your DR database will be unrecoverable.

The major drawback to using a different version of SQL Server for DR is that, once your DR site becomes live, YOU CANNOT REVERT BACK TO PRODUCTION because SQL Server 2005 backup files cannot be restored to a SQL Server 2000 instance.

There is also the possibility that your SQL Server 2000 database may have features that are no longer supported in SQL Server 2005 and you would not discover these until attempting to run your business from the DR server. If you chose to run SQL Server 2005 in DR, but SQL Server 2000 in production, you would certainly want to run the SQL Server Upgrade Advisor against your production database to be sure there weren't any compatability suprises waiting for you.

The right (and reliable) thing to do is to have your DR SQL Server version match your production SQL Server version: use SQL Server 2000 for your DR server, migrate your production SQL Server 2000 instance to SQL Server 2005 to match DR, or move your database from the existing SQL Server 2000 production instance to the other SQL Server 2005 production instance you mentioned.

more ▼

answered Oct 22, 2009 at 11:03 AM

KenJ gravatar image

KenJ
19.3k 1 3 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x473
x64
x11

asked: Oct 21, 2009 at 02:38 PM

Seen: 2262 times

Last Updated: Nov 20, 2009 at 08:58 AM