Have you ever had the scenario where your Log Shipping plan was interrupted? If so, how did you repair the broken Log Shipping Plan?
There have been a few issues seen recently on the SQLServerCentral forum whereby Log Shipping plans have been interrupted and the recommendation has been to completely re-initialise Log Shipping from Full and Log backups. The suggestion to resume the Log Shipping plan with a differential backup from the primary database was met with some scepticism, so this article seeks to address this and explain why this works. Having to re-initialise a Log Shipping plan can be a massive task especially when your database is a little on the large side, so any action that can minimise the effort and downtime has to be considered.
OK, so you know the Scenario: the junior DBA (as we never blame ourselves, right?) took a log backup outside of the Log Shipping plan and this backup was not restored to the secondary database. Instead it was deleted from its disk location and has been lost. Your pager starts bleeping and already the boss wants to know why no new data is being sent to the secondary reporting database.
You're about to re initialise the whole Log Shipping plan, which is a real killer as this database is a monster! Then you stop, you think and remember, didn't I read somewhere that a differential backup will bridge that LSN gap? Well, you're right and here's why it works.
Why Does the Differential Restore Work?
Why does the differential restore work even when there are continuous full backups occurring on the primary database?
The key here is the Log Shipping plan's 3 SQL Server agent jobs that backup, copy and restore the transaction log backups and constantly replicate data from the primary to the secondary. Embedded within this 'Replication' is the Differential Base LSN which is incremented inline with the primary database. Let's look at the following steps involved in the typical Log Shipping process and the Differential Base LSN points that are created.
| Step No | Step Name | Primary Diff BaseLSN | Secondary Diff BaseLSN |
| 1 | Log shipping implemented | 62000000061100036 | 62000000061100036 |
| 2 | First log backup runs, is copied and restored | 62000000061100036 | 62000000061100036 |
| 3 | Subsequent log backups copied and restored | 62000000061100036 | 62000000061100036 |
| 4 | Full backup occurs on primary database | 62000000064400043 | 62000000061100036 |
| 5 | Log backup taken, copied and restored | 62000000064400043 | 62000000064400043 |
What this means is that as long as log backups are restored to the secondary, the Differential base LSN remains in step allowing you to bridge LSN gaps between the primary and the secondary databases using a differential backup.
Looking at your typical backup configuration on your Log Shipped database you could well have the following in place
| Backup Type | Backup Frequency |
| Full | Every Sunday 20:00pm |
| Differential | Every night Monday - saturday 20:00pm |
| Transaction log | Via Log Shipping plan every 15 minutes during the day |
Given the scenario above, regular log restores will keep the secondary in step with the primary, any break in the Log Shipping plan may be bridged by restoring a differential backup. The only caveat here would be the following scenario;
- Sunday 19:30pm the Log Shipping plan breaks, a log backup is missing and the restore job is now skipping backup files. No new data is being sent to the secondary
- Sunday 20:00pm the Full backup occurs and completes successfully
- Monday 09:00am the DBA comes into work and wishes they'd stayed at home
- Monday 09:15am the DBA takes a differential backup of the primary and attempts to restore to the secondary but receives the following error message dialog.
Why is this?
If the full backup occurs after log shipping has broken but before you take the differential backup, no further logs are restored and the Differential Base LSNs are no longer synchronised, you will need to re initialise Log Shipping!
So, as we have already discovered, it's the transaction logs shipped and restored to the secondary that keeps the differential Base LSNs in step.
The situation above can be avoided to a certain extent by having suitable monitoring and notification to alert you immediately once a Log Shipping plan breaks. Production support (Business As Usual) DBAs can respond quicker to the alert and effect an immediate repair. You now just need to educate them on the process to be used.
As always, test thoroughly in your sandbox\test environment and if you're still stuck, post back I'll help all I can.
