Differntial Backup

Question
How exactly does the technology of a differntial backup work. I have read
msdn articles, etc... But nothing states in particular how the technology
itself operates. It doesn't appear to use the log file.
Since it is a single file (No secondaries used) how can it create a
differential file of updates that have occured since the last full backup?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.Hi
have you seen this link
http://msdn2.microsoft.com/en-us/library/ms345448(SQL.90).aspx
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||Paul
BOL says
differential database backup records only the data that has changed since
the last database backup. You can make more frequent backups because
differential database backups are smaller and faster than database backups.
Making frequent backups decreases your risk of losing data.

> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
The sequence for restoring differential database backups is:
1.. Restore the most recent database backup.
2.. Restore the last differential database backup.
3.. Apply all transaction log backups created after the last differential
database backup was created if you use Full or Bulk-Logged Recovery.
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||The GAM (global allocation map) keeps track of which Extents have been
changed since the last full backup. Those are the ones written by a
differential backup.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
> Question
> How exactly does the technology of a differntial backup work. I have read
> msdn articles, etc... But nothing states in particular how the technology
> itself operates. It doesn't appear to use the log file.
> Since it is a single file (No secondaries used) how can it create a
> differential file of updates that have occured since the last full backup?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.|||This is exactly what I was looking for.
Thanks
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
This posting is provided "AS IS" with no warranties or rights.
"Geoff N. Hiten" wrote:

> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||Actually it is the BCM (Bulk Changed Map) not the GAM but the same concept
applies. If a single bit on the extent was changed since the last full
backup this bit for that extent in the BCM will be flipped.
Andrew J. Kelly SQL MVP
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23NkgfLosHHA.1168@.TK2MSFTNGP02.phx.gbl...
> The GAM (global allocation map) keeps track of which Extents have been
> changed since the last full backup. Those are the ones written by a
> differential backup.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Paul Bergson" <pbergson@.allete.com.invalid> wrote in message
> news:B8054281-37AA-493B-977F-F345404260BD@.microsoft.com...
>|||A differential backup copies only the extents that were changed since the
last full backup was made. The extents are copied onto a specified backup
device.
SQL Server can quickly tell which extents need to be backed up by examining
the bits on the Differential Change Map DCM pages for each data file in the
database. DCM pages are big bitmaps, with one bit representing an extent in
a
file, just like the GAM and SGAM pages.
Each time a full backup is made, all the bits in the DCM are cleared to 0.
When any page in an extent is changed, its corresponding bit in the DCM page
is changed to 1.
Manu
"Paul Bergson" wrote:
[vbcol=seagreen]
> This is exactly what I was looking for.
> Thanks
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> This posting is provided "AS IS" with no warranties or rights.
>
> "Geoff N. Hiten" wrote:
>