locked
Log chain theory question RRS feed

  • Question

  • Hello!

    As per BOL:

    http://msdn.microsoft.com/en-us/library/ms186299(v=sql.105).aspx

    begins_log_chain bit 1 - This is the first in a continuous chain of log backups. A log chain begins with the first log backup taken after the database is created or when it is switched from the simple to the full or bulk-logged recovery model.


    As per http://msdn.microsoft.com/en-us/library/ms190440%28v=sql.105%29.aspx

    A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery.


    So what really starts a log chain - a DB or a LOG backup???

    Thank you in advance,

    Michael



    Tuesday, September 9, 2014 1:19 PM

Answers

  • Hello Michael,

    Reference 1 is indeed not precise in this part, because you can run first a log backup after the first full backup. If no full backup was taken before, a log backup will fail with an error.

    So a log backup chain starts with a full backup.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by MF47 Tuesday, September 9, 2014 1:55 PM
    Tuesday, September 9, 2014 1:28 PM

All replies

  • Hello Michael,

    Reference 1 is indeed not precise in this part, because you can run first a log backup after the first full backup. If no full backup was taken before, a log backup will fail with an error.

    So a log backup chain starts with a full backup.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by MF47 Tuesday, September 9, 2014 1:55 PM
    Tuesday, September 9, 2014 1:28 PM
  • Michael,

         The confusion falls in the fact that once a database has been changed from Simple to full or bulk logged recovery model then the transaction log will continue to truncate itself upon a checkpoint.  The reason is that SQL is smart enough to know that a database backup has not yet been taken so there is no possibility to recover the database to a point in time.  This is sometimes referred to as pseudo simple recovery.  After a database backup is taken then the transaction log will only be truncated after a transaction log backup.

    Hope this helps


    David Dye My Blog

    Tuesday, September 9, 2014 1:33 PM
  • Hello Olaf,

    Thank you for the reply!

    "If no full backup was taken before, a log backup will fail with an error." - yes, and I was surprised (to put it midly) reading the  opposite definition in MSDN...

    Regards,

    Michael

    Tuesday, September 9, 2014 1:47 PM
  • Hello, David,

    Thank you  for the clarification!

    But I think there should not be different definitions of the single concept in any case...especially in msdn.

    Regards,

    Michael

    Tuesday, September 9, 2014 1:55 PM