Unable to delete Log Shipping configuration

Question

I need some help with my SQL 2005 server. I am trying to setup log shipping, I've successfully done so for all user-databases, except one. From the looks of it, it once had log shipping configured until the secondary server was decommissioned before log shipping could be deleted. The old DBA deleted the old jobs.

So what's left is a database that is configured for log shipping, but the LSBackup job has been deleted. Which means, every time I try to open the log shipping configuration wizard, I am told that the job does not exist and to delete the configuration (Seems sensible). But I can't delete it because it is trying to connect to the secondary server... which does not exist so just times out and fails.

Any suggestions would be much appreciated!

Answer 1

If something is still setup on the secondary server you need to run sp_delete_log_shipping_secondary_database there.

You can clear the log shipping configuration on the primary by executing sp_delete_log_shipping_primary_secondary

USE master;
GO
EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database = N'<PrimaryDatabase>'
   ,@secondary_server = N'<SecondaryServer>'
   ,@secondary_database = N'<SecondaryDatabase>'
GO

Answer 2

After further investigation, I found the answer. I ran

Exec sp_delete_log_shipping_primary_secondary
@Primary_database = N'DATABASE_NAME'
,@secondary_server = N'SECONDARY_SERVER_NAME'
,@secondary_database = N'SECONDARY_DATABASE_NAME'

To delete all reference to that server.

followed by

EXEC sp_delete_log_shipping_primary_database
@database = N'DATABASE_NAME'

And it then worked :)

Answer 3

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 Lunch.

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