Note | Before deleting the log-shipping database, remove log shipping from it. When you remove log shipping, all schedules, jobs, history, and error information will be deleted. Again, 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. |
To use Management Studio to remove log shipping, follow these steps:
Choose the primary database properties.
Under Select a page, choose Transaction Log Shipping.
Clear the checkbox marked "Enable this as a primary database in a log shipping configuration," and click OK
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.
To remove a monitor server instance, uncheck the Use a monitor server instance checkbox.
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 the secondary information on the secondary server and its jobs by executing the sys.sp_delete_log_shipping_secondary_database_internal stored procedure:
Back on the primary server, issue this command:
Use Master; sp_delete_log_shipping_primary_database @database;
This command deletes the log-shipping information from the primary server and its jobs, removes monitor info and the monitor, and deletes the msdb.dbo.log_shipping_primary_databases table:
Then, if desirable, you can delete the secondary database.