Backup Chains

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 3, 2008 at 8:03 am

  #837869

  Steve Hindle (7/3/2008)

  ---

  Thanks you for all your replies.

  I have also been experimenting...

  I created:

  Full1.Bak

  Tran1.TRN

  Full2.BAK

  Tran2.TRN

  using SQL2005. I deleted Full2.BAK and restored Full1.Bak, Tran1.TRN and then Tran2.TRN and this worked.

  I'm still a tad confused?? I thought this would fail as the chain has been broken as a result of deleting Full2.BAK.

  The full2 backup between tran1 and tran2 does not break the backup chain from full1 through tran2. The reason for this is that full backups do not truncate the transaction log. A full backup backs up enough of the transaction log to ensure a consistant database when restored.

  😎

• Irish

  Hall of Fame

  Points: 3958

  More actions

  July 3, 2008 at 8:04 am

  #837871

  Steve,

  I was correct the first time, in this case. Gail further explained.

  As long as the following do not occur, you are in the clear :cool::

  1) The Recovery Model is changed between the last Full Backup is made and the next Transaction Log is taken (changing from Full to Bulk Logged for example breaks the chain)

  2) Some truncates the Transaction Log between the Full backup and the next Transaction Log, thus messing up the LSN's and breaking the chain

  The way you did it is the same way I tested it, and I had zero problems. The article does not do a very good job of explaining how it works.

  Regards, Irish 

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  July 3, 2008 at 8:07 am

  #837874

  Steve,

  Just think of the transaction log backups as being independent of the full log backups.

  As long as you have an unbroken "chain" of these, any full backup taken somewhere in the chain will do.

  You could go back to a full backup taken a year ago, as long as you have all the transaction log backups!