Have you ever found yourself in a situation where you have a large number of databases in log shipping and you need to remove all of them? If you’ve tried using SQL Server Management Studio, you know that it can be a time-consuming process. You have to manually go through each database, access its properties, navigate to the Transaction Log Shipping tab, choose to remove the secondary, uncheck the box, and finally hit OK. This becomes even more painful when dealing with a significant number of databases.

Fortunately, there is a more efficient way to remove log shipping using T-SQL. In the background, SQL Server executes stored procedures to remove the metadata from the log shipping related tables in the MSDB database. By leveraging these stored procedures, we can automate the process and save ourselves a lot of time and effort.

Let’s take a look at an example of how to remove log shipping using T-SQL:

-- Script for removal of Log Shipping from primary
SET NOCOUNT ON

DECLARE @ExecString VARCHAR(MAX)

SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_primary_secondary 
    @primary_database = N''Primary_Database_Name'',
    @secondary_server = N''Secondary_Server_Name'',
    @secondary_database = N''Secondary_Database_Name''' + ' go'
FROM msdb.dbo.log_shipping_primary_secondaries ps
JOIN msdb.dbo.log_shipping_primary_databases pd ON ps.primary_id = pd.primary_id

SELECT @ExecString

GO

DECLARE @ExecString VARCHAR(MAX)

SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_primary_database 
    @database = N''Primary_Database_Name''' + ' go'
FROM msdb.dbo.log_shipping_primary_databases

SELECT @ExecString

GO

The above script removes log shipping from the primary server. It retrieves the necessary details, such as the primary database name, secondary database name, and secondary server name, from the metadata tables. The script then generates an execute statement that can be copied and pasted into a new query window to remove log shipping.

Similarly, we can use the following script to remove log shipping from the secondary server:

-- Script for removal of LS from Secondary
DECLARE @ExecString VARCHAR(MAX)

SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_secondary_database 
    @secondary_database = N''Secondary_Database_Name''' + ' go'
FROM log_shipping_secondary_databases

SELECT @ExecString

GO

It’s important to note that the generated scripts should be carefully reviewed before running them on a production server. Always verify the details and ensure that you are removing log shipping from the correct databases and servers.

Automating tasks and harnessing the power of T-SQL can greatly simplify administrative tasks in SQL Server. Removing log shipping using T-SQL is just one example of how we can leverage the capabilities of SQL Server to streamline our workflows and save time.

Have you encountered similar situations in your environments? Feel free to share your experiences and any other T-SQL automation techniques you’ve used.