<

Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

Fixing log shipping when the primaray database does not exist anymore

Published on
12,693 Points
6,293 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Expert Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Join & Write a Comment

Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month