Have you ever come across a situation where you have large number of databases in log shipping and you have to remove all of them? If you use SQL Server Management Studio, it would take a long time because you have to Right Click on each database, go to properties, choose Transaction Log Shipping tab, choose remove for secondary, uncheck the box and then hit OK. Though monotonous, these are painful when the number of databases we are really huge.
In the background, it executes stored procedures to remove the metadata from the log shipping related tables in MSDB database.
Below is the sample which runs on primary. I have changed input parameters.
-- primary
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
EXEC MASTER.dbo.sp_delete_log_shipping_primary_database @database = N'Primary_Database_Name'
GO
And below runs on secondary (here also I have changed input parameter)
-- secondary
EXEC MASTER.dbo.sp_delete_log_shipping_secondary_database
@secondary_database = N'Seconday_Database_Name'
Essentially, if we want to remove log shipping we need to get primary database name, secondary database name and secondary server name. I have used metadata table to find that details.
-- Script for removal of Log Shipping from primary
SET NOCOUNT ON
GO
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = 'EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N''' + pd.primary_database +'''
,@secondary_server = N''' + ps.secondary_server+ '''
,@secondary_database = N''' + ps.secondary_database + ''''
+'
go'
FROM msdb.dbo.log_shipping_primary_secondaries ps,
msdb.dbo.log_shipping_primary_databases pd
WHERE 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+'''
go'
FROM msdb.dbo.log_shipping_primary_databases
SELECT @ExecString
GO
Once you run the script, you would get output with execute statement, just copy paste and run into new query window.
Here is the similar script for 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+'''
go'
FROM log_shipping_secondary_databases
SELECT @ExecString
GO
Note: Above scripts would generate which you need to copy paste in new query window on respective servers. Please verify before running the output on production server.
Trying to automate and use the power of T-SQL is one of the best things I have always felt. Do let me know if you did these sort of things in your environments? Can you share some of the experiences?
Reference: Pinal Dave (https://blog.sqlauthority.com)
9 Comments. Leave new
Thank you for this post.
Yeah, I semi-automate a lot of things like that, but I usually only use SELECT/EXEC statements to get the information, copy/paste the result into Excel, and create the UPDATE/EXEC statements with the string concatenating operators (&) in Excel, and copy/paste the result back to the query window where I execute it.
Hint: If an error occurs in Excel, saying the formula is too long – no problem, split the formula on two or more cells which is anyway easier to edit. The result of the formula can be a lot bigger than the 250 characters which is the maximum size of formulas (I never experienced any limititations).
e.g.
AA1: =”INSERT INTO dbo.[User] (User_LastName, User_GivenName) VALUES (”
AB1: =AA1 & “‘” & A1 & “‘, ‘” & B1 & “‘);”
Christoph Hafner – I agree with your solution. Excel can help in such situations.
Thx man, you are my man
How to setup :
SQL SERVER 2008 – Script – Adding Multiple Databases from instance 1 to Log Shipping on Instance 2 using scripts…..
Any one done this before, instead do using the GUI and doing it one at a time….
You can script the configuration while creating and then change parameters.
Will this delete all the log shipping jobs as well?
The scripts above will only bring back one row even if there are multiple databases configured for log shipping. Also the first script, to be executed against the primary, uses the deprecated old fashioned join syntax. The script below fixes both of these things but you will need to format the output before you execute it or it may generate errors when run.
— Generate scripts for removal of Log Shipping from primary
— RUN ON PRIMARY – queries from both panes, needs formatting after
SET NOCOUNT ON
GO
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = COALESCE(@ExecString + ‘EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N”’ + pd.primary_database + ”’
,@secondary_server = N”’ + ps.secondary_server + ”’
,@secondary_database = N”’ + ps.secondary_database + ”” + ‘
go ‘, ”)
FROM msdb.dbo.log_shipping_primary_secondaries ps
INNER JOIN msdb.dbo.log_shipping_primary_databases pd
ON ps.primary_id = pd.primary_id
SELECT @ExecString
GO
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = COALESCE(@ExecString + ‘EXEC master.dbo.sp_delete_log_shipping_primary_database @database = N”’ + primary_database + ”’
go ‘, ”)
FROM msdb.dbo.log_shipping_primary_databases
SELECT @ExecString
GO
—- Execute this against secondary —————–
— Script for removal of LS from Secondary
DECLARE @ExecString VARCHAR(MAX)
SELECT @ExecString = COALESCE(@ExecString + ‘EXEC master.dbo.sp_delete_log_shipping_secondary_database @secondary_database = N”’ + secondary_database + ”’
go
‘, ”)
FROM msdb.dbo.log_shipping_secondary_databases
SELECT @ExecString
GO
I’m getting the same single row entry with your script that I got with the first script
Nice👍