Tip: Upgrade SQL Server 2000 Log Shipping to SQL Server 2008

Unlike SQL Server 2000 log shipping, which is configured using maintenance plans, SQL Server 2008 log shipping is configured as part of the standard database properties. Because of this, you cannot directly update SQL Server 2000 log shipping to SQL Server 2008 log shipping. You can, however, migrate your SQL Server 2000 log shipping configuration to SQL Server 2008 log shipping.

To upgrade the log shipping configuration easily, complete the following steps:
1. Upgrade all secondary server instances to SQL Server 2008. When you upgrade the secondary server instances, any log shipping databases will remain SQL Server 2000 databases because they will be in an offline state.
2. Upgrade the primary server to SQL Server 2008. The primary database will be unavailable while the upgrade is in progress, and you will not be able to failover to a secondary server.
3. Enable the primary database for log shipping. To ensure that backup logs are applied properly, use the same backup share that you used with your SQL Server 2000 log shipping configuration.
4. Specify the secondary servers. In the Secondary Database Settings dialog box, you must select the option No, The Secondary Database Is Initialized during the configuration. The secondary database is upgraded automatically to a SQL Server 2008 database when you start shipping logs.

SQL Server 2008 does not use any of the log shipping tables used by SQL Server 2000. After the migration, you can remove the following SQL Server 2000 log shipping tables:

  • log_shipping_databases
  • log_shipping_monitor
  • log_shipping_plan_databases
  • log_shipping_plan_history
  • log_shipping_plans
  • log_shipping_primaries
  • log_shipping_secondaries

You can also delete any log shipping SQL Server Agent jobs created by SQL Server 2000.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant by William R. Stanek.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.