Thursday, January 17, 2013 5:07 PM
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?
George the DBA
Thursday, January 17, 2013 8:33 PMModerator
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
Sunday, January 20, 2013 12:58 AM
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.