SQL Server Log Shipping – Remove Configuration on Secondary Server When Primary is Unavailable

log-shippingsql serversql-server-2005

My primary server with SQL Server 2005 broke down, so I've started up the log-shipped database on the secondary server. The database is live on the secondary server, and I just need to remove the log shipping settings for it, while the primary server is no longer accessible.

It seems that the sp_delete_log_shipping_secondary_database stored procedure should be executed on the secondary server to do this, which "removes a secondary database and removes the local history and remote history", as it is stated on msdn.

Would this also remove the database itself? Or only the log shipping settings for it? And what will it do when the primary server is not accessible?

I must not loose the database on the secondary server, as it is already in use.

Best Answer

You are facing what is called orphaned entries in logshipping tables in msdb. You can use below tsql to just manually clean up logshipping orphan entries. For your case, since you don't have the primary database available, you should just run the secondary part or you can try running sp_delete_log_shipping_secondary_database N'secondary_database'. It will just remove the secondary database settings from msdb.

----- on primary
delete from [dbo].[log_shipping_monitor_primary]
where primary_database = 'your database name'       -- Change the database name 
go
delete from [dbo].[log_shipping_primary_databases]
where primary_database = 'your database name'        -- Change the database name
go
delete from [dbo].[log_shipping_primary_secondaries]
where secondary_database = 'your database name'      -- Change the database name


----- on Secondary
delete from [dbo].[log_shipping_monitor_secondary]
where secondary_database  = 'your database name'     -- Change the database name
go
delete from [dbo].[log_shipping_secondary]
where primary_database = 'your database name'        -- Change the database name
go
delete from [dbo].[log_shipping_secondary_databases]
where secondary_database = 'your database name'      -- Change the database name

Note: Above methods are safe and will delete ONLY logshipping related information.

Related Question