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:
|
1 |
EXEC sp_delete_log_shipping_primary_secondary 'PrimaryDatabaseName', 'SecondarySqlServer', 'SecondaryDatabaseName'; |
If the previous command does not effectively remove the log-shipping configuration on the PRIMARY, use the command below:
|
1 2 |
-- Manually removes the log-shipping for the database specified on the primary SQL Server. EXEC sp_delete_log_shipping_primary_database 'PrimaryDatabaseName'; |
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.
|
1 |
SELECT * FROM msdb.dbo.log_shipping_primary_databases |
Then run this on the SECONDARY SQL server:
|
1 |
EXEC sp_delete_log_shipping_secondary_database 'SecondaryDatabaseName'; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Priomary SQL Server SELECT * FROM msdb.dbo.log_shipping_primary_databases EXEC sp_delete_log_shipping_primary_secondary 'PrimaryDatabaseName', 'SecondarySqlServer', 'SecondaryDatabaseName'; -- Manually removes the log-shipping for the database specified on the primary SQL Server. EXEC sp_delete_log_shipping_primary_database 'PrimaryDatabaseName'; SELECT * FROM msdb.dbo.log_shipping_primary_databases -- Secondary SQL Server SELECT * FROM msdb.dbo.log_shipping_primary_databases EXEC sp_delete_log_shipping_secondary_database 'SecondaryDatabaseName'; SELECT * FROM msdb.dbo.log_shipping_primary_databases |
Views – 2149