<

Go Premium for a chance to win a PS4. Enter to Win

x

Fixing log shipping when the primaray database does not exist anymore

Published on
12,793 Points
6,393 Views
4 Endorsements
Last Modified:
Brian Chan
Information does not come for free...
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A few minutes later, alerting emails start to flood my phone - "Hi there, Log Shipping is broken........." I made a wrong assumption that log shipping would be just intelligent enough to work with the changes. Think about it again -- with the way log shipping was configured, how would it know the database name was changed and reconfigured? I should have turned off log shipping first. Shame on me.

So, what's next? The application is back online so there is no turning back (or the manager would be very upset). Here is what can be done to rectify the messy situation.  I researched on what to do, and then this MSDN URL came through:

How to: Remove Log Shipping (Transact-SQL)

So firstly, connect to the primary database server to break the relationship between the primary and secondary database. This is done by running the built-in stored procedure named sp_delete_log_shipping_primary_secondary.  Here's the example:

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'DB1'
,@secondary_server = N'PrimaryServer'
,@secondary_database = N'DB1_DR'

Open in new window


Easily done without hassle. Then, connect to the secondary server and remove the log shipping job information with the stored procedure named sp_delete_log_shipping_secondary_database, again here is the code:

sp_delete_log_shipping_secondary_database
@secondary_database = 'DB1_DR'

Open in new window


Done. Then, it's time to do this the other way around. Connect to the primary server and remove the log shipping job information with the stored procedure named sp_delete_log_shipping_primary_database, here is the example:

sp_delete_log_shipping_primary_database
@database = 'DB1'

Open in new window


Up to this point, you have killed the log shipping job at both end. Great, but hang on, email is still flooding in. Have you set up witness server to monitor log shipping? OooooK. Yes, you need to remove some entries on the monitoring server as well. First, note down the primary job ID and the secondary job ID like the following:

EXEC sp_help_log_shipping_monitor_primary
@primary_server = 'PrimaryServer',
@primary_database = 'DB1'

EXEC sp_help_log_shipping_monitor_secondary
@secondary_server = 'SecondaryServer',
@secondary_database = 'DB1_DR'

Open in new window


Once you got the IDs noted, go to disable it from monitoring or delete them from the tables:

--Check the ID against the table by selecting only the combination of primary server and primary database
SELECT *
FROM msdb..log_shipping_monitor_primary
WHERE primary_server = 'PrimaryServer'
AND primary_database = 'DB1'

-- Here is the choice. Disable it, or delete it. your call
--UPDATE msdb..log_shipping_monitor_primary SET threshold_alert_enabled=0 WHERE primary_id ='E244490E-1ACA-470B-8C5A-B99F7B817EC4'
--DELETE FROM msdb..log_shipping_monitor_primary WHERE primary_id ='E244490E-1ACA-470B-8C5A-B99F7B817EC4'

-- make sure it is gone.
SELECT *
FROM msdb..log_shipping_monitor_primary
WHERE primary_server = 'PrimaryServer'
AND primary_database = 'DB1'


--Check the ID against the table by selecting only the combination of secondary server and secondary database
SELECT *
FROM msdb..log_shipping_monitor_secondary
WHERE secondary_server='SecondaryServer' and secondary_database='DB1_DR'

-- Same deal here, disable or remove it
--UPDATE msdb..log_shipping_monitor_secondary SET threshold_alert_enabled=0 WHERE secondary_id ='CC8A539C-59AA-4304-979C-33E56503C0A8'
--DELETE FROM msdb..log_shipping_monitor_secondary WHERE secondary_id ='CC8A539C-59AA-4304-979C-33E56503C0A8'

-- make sure it is gone.
SELECT *
FROM msdb..log_shipping_monitor_secondary
WHERE secondary_server='SecondaryServer' and secondary_database='DB1_DR'

Open in new window


At this point, you are clean of previous log shipping relationship. The past should be behind you.  Now, remember to set up the log shipping again for the new database.
4
Comment
Author:Brian Chan
1 Comment
 

Expert Comment

by:Angel Vyang
Thank you so much. Very nice article. It's worked for me.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month