Friday, April 11, 2014

Log shipping breaks what to do now

Log shipping breaks what to do now :

1.       Either we can reconfigure log shipping using full and log backup.

2.       Or if lucky we can take one differential backup and keep log shipping active but there is a catch:
We must make sure there is no full backup performed on primary database after log shipping breaks and before taking differential backup.

Reason :

A differential backup can be restored on database
a.       After last full back up has been restored on secondary server  with norecovery or
b.      Any previous(old) full backup together with all subsequent log backup and atleast one log backup after last full backup and before final diff backup taken has been restored before another full backup

Consider the following scenario for restoring differential backup (DF3) take at today (T) at 7 PM
DB1                                                                                                                                        DB2

Backup type (Base LSN)
Restored database (Base LSN)
Full backup  (..031) FB1
Log backup  (..031)  LB1
Log backup  (..031)  LB2
Diff backup  (..031) DB1
Full backup  (..045) FB2
Not restored  (..031)
Log backup  (..045) LB3
Log backup  (..045) LB4
Full backup  (..056) FB3
Not restored  (..45)

Assume no other backup taken apart from above backup and diff backup taken today.
We can restore diff backup (DF3) if

1.       FB3 backup not taken (FB1 + LB1+LB2+LB3+LB4) + DF3

2.       FB3 backup not taken (FB1 + LB1+LB2+LB3) + DF3

3.       FB3 backup not taken (FB2+ LB3+LB4) + DF3

4.       FB3 backup not taken (FB2+ LB3) + DF3

5.       FB3 backup not taken (FB2) + DF3

6.       FB3 backup taken but not restored DF3 won’t work with any combination and will be invalid until we restore the FB3.

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.

No comments:

Post a Comment