Post reply

Doubt on LSN

  • September 26, 2011 at 2:14 am

    #390470

    Hi All,

    Was testing with the backups. Took the backups in the following sequence.

    1. Full Backup

    Inserted some records.

    2. Log Backup (Deliberately backed up LOG before DIFF)

    3. Differential Bakcup.

    I checked the FirstLSN and LastLSN for the above 3 backup files and got the following.

    FirstLSN LastLSN

    -------------------------------------------------

    Full4700000004120000047000000043000000

    Log4700000004120000065000000040800000

    Diff6500000004250000065000000042700000

    Now my doubt is that, I was expecting the "FirstLSN" of differential backup to be the "LastLSN" of Full backup.

    But here am seeing something else. Why this descrepency ?

    Or did I understand the backup process wrongly ?

    Thanks in advance.

    San.

  • September 26, 2011 at 3:40 am

    #1386338

    The first and last LSNs of the diff, just like of the full backup, are of transactions running during the backup.

    A log backup contains a set of log records, a diff backup contains the data pages modified since the last full backup. The log backup can be used wit STOPAT to stop at any LSN between the First and Last, the diff backup can only restore to the point the diff backup was taken.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 27, 2011 at 10:10 am

    #1387303

    Gail,

    Can you please clarify this a bit unusual case (I mean Full-TrLog-Diff backups)?

    Let's assume there were no transactions during backups (only some inserts between Full and TrLog as originally stated).

    Can we restore DB using only Full and Diff backups (i.e. skipping TrLog)?

    It seems like we can but there is big gap in LSNs between last_LSN for Full and first_LSN for Diff backups-

    How does Engine handle this gap?

    Thanks, Yuri

  • September 27, 2011 at 10:20 am

    #1387312

    Yuri55 (9/27/2011)

    Gail,

    Can you please clarify this a bit unusual case (I mean Full-TrLog-Diff backups)?

    This isn't at all an unusual case...

    Let's assume there were no transactions during backups (only some inserts between Full and TrLog as originally stated).

    Can we restore DB using only Full and Diff backups (i.e. skipping TrLog)?

    Of course. You'll be restoring to the point that the differential finished.

    It seems like we can but there is big gap in LSNs between last_LSN for Full and first_LSN for Diff backups-

    How does Engine handle this gap?

    It's not a gap. All those are showing, for full and diff backups is the LSN at which the backup started (the actual start of the backup database command) and the LSN at which the backup finished (the actual end of the backup database). They're not in any way showing what range of LSNs the backups cover. Only for log backups does the start and end LSN show what's in the backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 27, 2011 at 10:38 am

    #1387327

    They're not in any way showing what range of LSNs the backups cover. Only for log backups does the start and end LSN show what's in the backup.

    Now it makes sense. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply