Disabling Log Shipping When the Secondary Server Doesn't Exist

By David Benoit in Theophilus | 01-20-2009 2:43 PM | Categories: Filed under: Log Shipping

Rating: |

Discuss | 2,362 Reads | 175 Reads in Last 30 Days |4 comment(s)

Had a bit of an oddity show up during some testing today when trying to disable log shipping on the primary server using SSMS. My test scenario was such that our secondary server was not available any longer (had been renamed) and when I tried to disable the log shipping from the primary database using SSMS it would attempt to connect to the secondary server which of course would result in a failure. So, went to BOL and found that I can run the following and this will take care of the clean-up quite nicely.

These need to be run on the primary server.

sp_delete_log_shipping_primary_secondary
        @primary_database = 'TestLogShip'
        , @secondary_server = 'TstSecServer'
        , @secondary_database = 'TestLogShip'

sp_delete_log_shipping_primary_database 'TestLogShip'

My guess is that SSMS is trying to execute sp_delete_log_shipping_secondary_database as well which will force you to connect to the secondary server so that the secondary database can be dropped but it didn't even ask that as an option (that I noticed). Regardless, the stored procedures worked great!

Comments

Steve Jones said:

I think the SSMS limitation is there to protect people from removing this when they didn't mean to.

Still, a nice tip to keep in mind.

January 21, 2009 3:35 AM

rodjkidd said:

That is pretty well timed for me. I going to be moving the secondary server in a log shipping setup next week! And this may well come in handy!

Cheers,

Rodders...

January 22, 2009 2:12 AM

H said:

I wonder, will this also be an issue in case of a Database Mirror setup (as this relies on log shipping)?

regards,

Hans

January 23, 2009 2:40 AM

David Benoit said:

Hans,

There are different stored procedures and process for making the mirror principal. I had to muddle through it about a month ago. Maybe another post in the future. ;)

David

January 23, 2009 2:51 PM