Log Shipping between SQL 2000 and SQL 2005 is not possible

  • Article

I thought of posting this quick blog entry today.

I work with many Microsoft's customers - big and small. In the past few weeks, many customers have asked me this question:

"Can we configure Log Shipping between SQL 2000 and SQL 2005?"

To the surprise of many, this is NOT possible.

For the sake of discussion, let’s consider that you want to configure log shipping in the following scenarios:

- SQL Server 2000 as the source server and SQL Server 2005 as the standby destination server

Use the Database Maintenance Plan Wizard to configure Log Shipping. During this wizard, you will reach the “Specify the Log Shipping Destinations” where you can click on ADD button to add the servers.

Under the Server Name drop down, you will ONLY see those servers that are already registered in your Enterprise Manager. Since you cannot register a SQL 2005 server under Enterprise Manager, it will never pop-up in the drop down menu, thereby not giving you any option to setup log shipping in this scenario.

Now, if you try using SQL Server 2005 Management Studio, you still cannot configure log shipping. Register a SQL 2000 server under Management Studio and right click on the SQL 2000 database.

Here you won’t find the option “Ship Transaction Logs” under TASKS. This is by design.

- SQL Server 2005 as the source server and SQL 2000 as the standby destination server

Now, let’s reverse the scenario. This is a strict no-no and is anyhow not possible by design.

In your SQL Server 2005 Management Studio, right click on a SQL 2005 database and go to TASKSà Shipt Transaction Logs. When you enable the database for log shipping, you can add Secondary servers. If you try to add a secondary server that is SQL 2000, it will throw the following error message:

“servername” is not a valid secondary server instance because it is not the correct version. Secondary server instances must be servers running SQL Server 2005 or later.

By design, both these scenarios are not possible. You can although think of some custom solution of your own, but this is not a possibility in SQL 2005 out of box.

Hope this helps.