I had some SQL SERVER 2016 databases log shipping to a secondary server, also SQL SERVER 2016 then I lost the secondary server due to a problem in the virtual machine host.
I have used the script below to generate all the scripts I need in order to clear my databases from the log shipping configuration:
select STEP1='USE master;' + CHAR(13) +
'EXEC master.dbo.sp_delete_log_shipping_primary_secondary' + char(13) +
' @primary_database = N' + '''' + a.primary_database + '''' + char(13) +
',@secondary_server = N' + '''' + 'QG-V-SQLDEV2-TS' + '''' + char(13) +
',@secondary_database = N' + '''' + a.primary_database + '''' + char(13)
,STEP2='USE master;' + CHAR(13) +
'EXEC master.dbo.sp_delete_log_shipping_primary_database @database= N'
+ '''' + a.primary_database + '''' + char(13)
from msdb.dbo.log_shipping_primary_databases a
I even left the name of my old secondary server there, which you would need to replace to the name of your old secondary server.
that script above generates the output below in my current environment:
Just copy the
step1 and the
step2 and run then at once, for example:
USE master; EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = N'Lunch' ,@secondary_server = N'QG-V-SQLDEV2-TS' ,@secondary_database = N'Lunch'
USE master; EXEC master.dbo.sp_delete_log_shipping_primary_database @database= N'Lunch'
that got rid of the log shipping configuration for database
To check if the setting has really gone, just run the first script again, no reference to the
Lunch database should be there!!
Alternatively, to get the list of databases involved in the log shipping use the following script:
select * from msdb.dbo.log_shipping_primary_databases