This post was formerly published at my beloved old blog "On the quest of a DBA's adventure......"
Naughty Me. While I am changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notify my application fellow that I am done here. They turn on the application, everything is working. Few minutes later, alerting emails start to flood my phone - Hi there, Log Shipping is broken......... OOOooo, Shxt. I should turn off log shipping first. Shame on me.
So, What's next? Application is back online so it is no turning back. Here is what can be done to rectify the messy situation. Try to research on what to do and then this MSDN URL come 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. It is done by running the built-in stored procedure named sp_delete_log_shipping_primary_secondary, 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_secondary_database, 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_primary_database, here is the example:
Up to this point, you have kill the log shipping job at both end. Great, but hang on, email is still flooding in. Have you set up witness server to monitor logshipping? 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 delete them from the tables:
At this point, you are clean of ex-log shipping relationship. The past should be at your back and now remember to set up log shipping again for the new database.
No comments:
Post a Comment