Manually removing log-shipping

I have had a number of situations where log-shipping broke and I needed to rebuild it but could not drop the log-shipping configuration from the primary SQL server. This often happens in my work environment where we have multiple business locations and I have found that I needed to use an FQDN or IP address (not recommended) when log-shipping across domains. Described below is how you do it manually:

On the PRIMARY SQL server, run this command for every database you need to manually drop the log-shipping from. This is for cases when the log-shipping databases are still intact and log-shipping is still working:

If the previous command does not effectively remove the log-shipping configuration on the PRIMARY, use the command below:

You can verify that the database has been removed with this sproc. If you execute this sproc and your database no longer displays in the list, the “LSAlert_PrimarySqlServer” will no longer send alerts every 15 minutes.

Then run this on the SECONDARY SQL server:

Once this completes, you can then begin to rebuild the log-shipping with the GUI.

[TODO] I will eventually add to this post, or write a new one, on building log-shipping manually. This will be a MUCH longer post as there are a lot of tasks to perform building log-shipping. Maybe I’ll even write some PowerShell cmdlets to simplify the task (there are cmdlet’s to build log-shipping but it is a bit or work)

Here is the full script:

Views – 581