Fixing log shipping when the primaray database does not exist anymore

Published:
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
9,408 Views

Comments (1)

Thank you so much. Very nice article. It's worked for me.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.