1

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!

  • Is that an Agent job? If so, can you copy a job from another database and fool the wizard into believing it does exist? – Andomar May 20 '15 at 8:03
  • How could I fool the wizard if it is expecting a unique job ID? – Gareth May 20 '15 at 8:08
4

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
  • Hi Sporri, Thank you but as my question states, the secondary doesn't exist anymore. – Gareth May 20 '15 at 12:22
  • Then just execute sp_delete_log_shipping_primary_secondary on the primary server with @primary_Database = 'DBNAME' – Spörri May 20 '15 at 12:23
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 :)

0

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:

enter image description here

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.