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:
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_pri
dary. Here's the example:
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_sec
abase, again here is the code:
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_pri
ase, here is the example:
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:
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_primaryWHERE 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_primaryWHERE primary_server = 'PrimaryServer'AND primary_database = 'DB1'--Check the ID against the table by selecting only the combination of secondary server and secondary databaseSELECT *FROM msdb..log_shipping_monitor_secondaryWHERE 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_secondaryWHERE secondary_server='SecondaryServer' and secondary_database='DB1_DR'