Remove Log Shipping from Production server with Secondary server
In Microsoft SQL Server Management Studio
1. “Start” > “Microsoft SQL Sever Tools 17” > “Microsoft SQL Server Management Studio 17”
2. Connect to Production Server > Expand “Databases”
3. Right-Click “LOG_SHIPPING” DB > “Properties”
4. Select “Transaction Log Shipping” > Clear “Enable this as a primary database in a log shipping configuration”

Remove Log Shipping from Production server without Secondary server
When Secondary server does not existed (e.g. power-off, disconnected), we will unable to remove log shipping from GUI.
We need to use TSQL to remove Log Shipping.

USE master
GO
EXEC sp_delete_log_shipping_primary_secondary @primary_database = 'LOG_SHIPPING', @secondary_server = 'DB02', @secondary_database = 'LOG_SHIPPING';
GO
EXEC sp_delete_log_shipping_primary_database @database = 'LOG_SHIPPING'
GO

Remove Log Shipping from Secondary server without Production server

EXEC sp_delete_log_shipping_secondary_database 'LOG_SHIPPING'
-- After remove log shipping configuration from Secondary server,
-- we may need to bring the DB to production.
RESTORE DATABASE LOG_SHIPPING WITH RECOVERY

Rebuild Log Shipping

Prepare for Rebuild log shipping
1. Insert some data at Production server
INSERT INTO dbo.LOG_SHIPPING_Table_A(Value_A) VALUES (‘Resync Log Shipping’)
INSERT INTO dbo.LOG_SHIPPING_Table_B(Value_B) VALUES (‘Resync Log Shipping’)

2. Create Shared Folder for full backup
At Shared Folder at Production Server
1. “NT Service\SQLSERVERAGENT” & “NT Service\MSSQLSERVER” have full control
2. Computer account of Secondary Server have Read ONLY

How to Rebuild Log Shipping

1. Disable all Log Shipping jobs
a. Backup job at Production server
b. Copy job at Secondary server
c. Restore job at Secondary server

2. Create a Full Backup of production DATABASE

BACKUP DATABASE LOG_SHIPPING TO DISK ='\\DB01\Resync\Full_BK.BAK'

3. Store Full Backup at Secondary server

RESTORE DATABASE LOG_SHIPPING FROM DISK ='\\DB01\Resync\Full_BK.BAK' WITH NORECOVERY

4. Enable all Log Shipping jobs
a. Backup job at Production server
b. Copy job at Secondary server
c. Restore job at Secondary server