How to Manually Remove Log Shipping May 16, 2018
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:
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:
@primary_database = N’AdventureWorks’,
@secondary_server = N’LogShippingServer’,
@secondary_database = N’LogShipAdventureWorks’
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:
On the secondary server, we then need to execute sp_delete_log_shipping_secondary_database to delete the secondary database, as follows:
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!