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

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.

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.