<

Fixing log shipping when the primaray database does not exist anymore

Published on
13,311 Points
6,911 Views
4 Endorsements
Last Modified:
Approved
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

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month