Clear Log Shipping after a server or database failure

If you have Transaction Log Shipping configured at your environment and one of the servers or databases fails then there is no way to remove Log Shipping using the GUI. The only way to clear the Log Shipping is using T-SQL script.

There are three steps in total that needs to be run to remove the Log Shipping from the Primary and from the Secondary database. If the Secondary server is completely lost just run Step 1 & 2 to clean the Log Shipping.

Step 1. Run this to the Primary server. It cleans all information about the secondary database.

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'primarydatabase',
@secondary_server = N'primaryserver',
@secondary_database = N'secondarydatabase'
GO

Step 2. Run this to the Primary server. It cleans the Log Shipping configuration from the Primary server & the SQL Agent backup job.

EXEC master.dbo.sp_delete_log_shipping_primary_database @database = N'primarydatabase';
GO

Step 3. Run this to the Secondary server. It cleans the Log Shipping from the Secondary database

EXEC master.dbo.sp_delete_log_shipping_secondary_database @secondary_database = N'secondarydatabase'
GO

 

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.