Wednesday, 20 November 2013

Removing Logshipping infomation from Primary and secondary servers

Many times, there is a need to log ship a database to a second server for upgrading or testing.

Once the logshipping is stopped and the database brought online, there can often be settings left behind.  This will cause reports, or checks to show errors.  (You all have checks in place to look for logshipped databases that are out of sync, right?)

So how can it be removed?

Here goes:-

On the primary (must be run in master)

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'AdventureWorks'
,@secondary_server = N'PRIMARY\STANDBY'
,@secondary_database = N'AdventureWorks'

On the secondary (also in master)


THIS WILL NOT DELETE THE DATABASE, its just bad wording on Microsoft's part.

Then if needed delete the secondary database.