Log Shipping between Sql 2000 and Sql 2005

mholden

We currently have some apps running on Sql Server 2000 and some on 2005. Is it possible to ship logs from sql server 2000 databases and sql server 2005 databases to a single sql server 2005 database server? Can we mix like that? Also, are there any requirements to the version(standard, enterprise, etc) of sql server that is installed for sql server 2000 and 2005 in order to take advantage of Sql Backup's Log Shipping capabilities?

Chris Auckland

Thanks for your post.

It will be possible to log ship from SQL 2000 to SQL 2005, as it is possible to restore SQL 2000 transaction logs to a SQL 2005 database. However, it is probably important to note that if you need to bring the SQL 2005 database online in a disaster recovery situation, then once the original SQL 2000 server is back online, you will not be able to restore the standby SQL 2005 database on the SQL 2000 server. This is because even though the transaction logs are from a SQL 2000 database, once they are restored to SQL 2005 it will become a SQL 2005 database, and you are unable to restore a SQL 2005 database on a SQL 2000 server.

The different versions of SQL Server do not really make a difference to the way we implement log shipping. All you really need is the ability to perform transaction logs backups, and the presence of the SQL Agent to schedule the job. All full versions of SQL Server have this facility, it is only SQL Express that falls short because it does not have the SQL Agent.

I hope this answers your question.

Let me know if you need any more information.

petey

See here for an overview of the SQL Backup log shipping process.

In your scenario, the only limitation I can think of is that your SQL Server 2000 databases cannot be put in read-only mode (STANDBY = ...) on the SQL Server 2005 server during the log shipping process. Only the NORECOVERY option is supported. This is a SQL Server requirement.