Cleanup Log-shipping configuration through TSQL

by

For database migrations, DBAs many times configure log shipping from old server to new server to reduce the downtime required during the cutover. Below approach is followed to perform such migrations

  1. Configure log shipping between old server and new server
  2. During cutover,
    1. Stop application
    2. Manually run LS backup job followed by copy and restore job.
    3. Offline databases on primary instance (old server)
    4. Bring DBs online on secondary instance (new server)
    5. Point application to new server
    6. Perform application testing
    7. If application testing is successful,  perform LS cleanup

As in above approach, we offline our databases on primary instance , we can no longer remove the log shipping configuration using GUI through database properties. In this case, we need to use T-SQL scripts to perform cleanup of log shipping configuration from system tables related to log shipping and removing related jobs etc.

For this, execute below scripts in order.

On Primary Server:

On primary instance, we need to execute “sp_delete_log_shipping_primary_secondary” followed by “sp_delete_log_shipping_primary_database” to  cleanup log shipping configuration from primary instance.

Please replace primary_database, secondary_database and secondary_instance with actual names.

EXEC master.dbo.sp_delete_log_shipping_primary_secondary @primary_database = N'primary_database' ,@secondary_server = N'Secondary_instance' ,@secondary_database = N'secondary_database';
GO

Use master
GO
sp_delete_log_shipping_primary_database [ @database = ] 'primary_database'

On Secondary Server:

Execute below command on secondary instance by replacing ‘Secondary_database’ with actual name of secondary database.

sp_delete_log_shipping_secondary_database [ @secondary_database = ] 'secondary_database'

Once above scripts will be executed successfully , log shipping configuration and history will be removed from log shipping related tables and corresponding jobs will get deleted.

Hope you will find this article helpful.

Happy learning!!

Post Views: 113