SQL Server 2008 R2 Differential Backup: Broken Chain

Question

I have a strange Diff Backup issue on SQL Server 2008R2:

here is server @@version:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

SQL Server VSS writer service is disabled, to avoid issues caused by Windows backup.

This database has multiple FileGroup and Multiple files in each FG; size around 200GB in total. here is the database Diff Backup Base info:

select distinct differential_base_lsn, differential_base_guid, differential_base_time from RSRegional.sys.database_files where type<>1
121899000002098900181    BE0CB427-DF92-4806-B1D8-7CABC19CDB1B    2013-01-17 05:26:54.657

Full Backup happened at midnight  every day and log backup every half hour. I tried couple of time Diff backup and you can see the lsn sequence is broken:

select top 100 backup_set_id, type, backup_finish_date, first_lsn, last_lsn from backupset where database_name='RSRegional' order by backup_set_id desc
31600    I    2013-01-17 11:42:12.000    121998000005999800078    121998000006229500001
31589    L    2013-01-17 11:30:11.000    121985000001569800001    121996000004151500001
31586    I    2013-01-17 11:22:53.000    121991000000606700260    121991000000621100001
31585    I    2013-01-17 11:20:59.000    121991000000210400250    121991000000228200001
31573    L    2013-01-17 11:00:08.000    121979000005847100001    121985000001569800001

...(log backup info, once per 1/2 hour)

31336    L    2013-01-17 01:30:03.000    121905000001386900001    121906000006436400001
31334    I    2013-01-17 01:26:33.000    121906000004322000147    121906000004761100001
31322    L    2013-01-17 01:00:07.000    121900000000423000001    121905000001386900001
31320    D    2013-01-17 00:59:21.000    121899000002098900181    121905000000905100001

The issue looks as

1. diff backup base lsn is Full DB first lsn. it should be the last lsn from my understanding?

2. each diff backup shows different first lsn.

How come? can some one help?

Thanks

George

---

George the DBA

Answer 1

You aren't the first to stumble across this issue.  Anyone who writes a log shipping script that uses the msdb backupset table and actually tests using differential backup learns that finding the correct log to apply after the restore will require a different algorithm depending on whether the prior restore was a full, a log, or a differential. 

#1 The reason the diff is based on the full backup first lsn is that is when the GAM snapshot is taken and the DCM is initialized. 

#2 Differentials contain the changed extents from the base database backup plus enough log records to roll forward to when the differential backup completed.  So when you restore a differential, it simple overwrites the extents in the DCM, then plays the log segment taken while those pages were being copied out of the original system.  The log backups have to be sequential, but full and differential backups are independent and do not affect the log backup sequence chain. 

---

Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

Answer 2

Hi there,

your differencial back up contains all the log back up information from your funnback up. so it is not really mattere if your log number changes.

your next differencial backup contains all the log backup information form your fullbackup.

For this reason your differential backup grows faster.

if you want to implement a program to restore here are the steps:

1) restore your recent fullbackup or based on the date that day full backup and restore with no recovery

2) find most recent differencial backup and restore with no recovery.

3) find all lsn backups after your recent differential backup and restore in order with no recovery.

4)finally restore the tail of the transaction log with recovery.

thanks

kumar