How to Manually Remove Log Shipping

Removing log shipping via SQL Management studio is as simple as un-selecting the log shipping check box under the database properties, however every now and again somebody restores a database over a DB that was configured for log shipping, consequently breaking everything and now requires a little manual intervention in order to get things cleaned up again.

Long story short, there are 3 steps to manually removing log shipping:

Step 1.
On the primary server, execute sp_delete_log_shipping_primary_secondary to delete the information about the secondary database from the primary server. An example of the syntax for this is as follows:

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N’AdventureWorks’,
@secondary_server = N’LogShippingServer’,
@secondary_database = N’LogShipAdventureWorks’
GO

Step 2.
Execute sp_delete_log_shipping_primary_database to delete information about the log shipping configuration from the primary server. This will also remove the SQL Agent backup job:

sp_delete_log_shipping_primary_database N’AdventureWorks’

Step 3.
On the secondary server, we then need to execute sp_delete_log_shipping_secondary_database to delete the secondary database, as follows:

sp_delete_log_shipping_secondary_database N’LogShipAdventureWorks’

Hope this helped you get out of a bind. Post a comment below if this helped or if you have any recommendations or improvements on this!