Professional Microsoft® SQL Server® 2008 Administration

19.10. Removing Log Shipping

Before deleting the log-shipping database, remove log shipping from it. When you remove log shipping, all schedules, jobs, history, and error information are deleted. Recall that there are two ways to remove log shipping: with Management Studio and with T-SQL. You may want to script the log-shipping configuration before deleting to quickly redeploy log shipping in the future.

19.10.1. Removing Log Shipping with Management Studio

To use Management Studio to remove log shipping, follow these steps:

1. Choose the primary database properties.

2. Under Select a Page, choose Transaction Log Shipping.

3. Clear the checkbox marked "Enable this as a primary database in a log shipping configuration," and click OK.

4. You can also choose to remove a secondary server from the primary server's database properties. Under Secondary Databases, choose the secondary server instance and click Remove.

5. To remove a monitor server instance, uncheck the "Use a monitor server instance" checkbox.

19.10.2. Removing Log Shipping with T-SQL Commands

To remove log shipping with T-SQL, issue this command on the primary server:

Use Master;
sp_delete_log_shipping_primary_secondary  @primary_database, @secondary_server,
@secondary_database;

This command deletes secondary information on the primary server from the Msdb.dbo.log_shipping_primary_secondaries table:

On the secondary server, issue this command:

Use Master;
sp_delete_log_shipping_secondary_database @secondary_database;

This command deletes ...