How sql server understands from which point the differential backup should be taken??????

Question

How sql server understands from which point the differential backup should be taken??????

amarbreddy21

SSC Veteran

Points: 275
More actions
February 11, 2009 at 7:30 am

#205327

How sql server understands the point at which the full backup is completed
how it recognizes and takes a differential backup from the point, the full backup has completed.
does it get stored into some table.

Viewing 8 posts - 1 through 7 (of 7 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

SQL Server records the first and last log sequence number (LSN) which is included in a backup. This information is stored in the msdb databases in the backupset table.

This article gives a good explanation about the different type of backups:

http://www.teratrax.com/articles/sql_server_backup_types.html

[font="Verdana"]Markus Bohse[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 2

Say you have a big database, and you know from experience it takes 4 minutes to do a complete backup.

the last thing the backup would include would be every commited transaction..up to that last second....at the very end of those 4 minutes...

so a backup is absolutely up to date from when when the timestamp of the backup is complete....no gaps due to "when it started" vs "when it completed"

the backup is grabbing all the static data along with the changes that exist in the transaction log....so the last thing the backup is doing is adding any changes that exist in the transaction log up to the last moment of the back....does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

amarbreddy21 SSC Veteran Points: 275 More actions · Answer 3

Markus:

SQL Server records the first and last log sequence number (LSN) which is included in a backup

This LSN is stored only for backing up transaction logs r does it get

stored for Diff. backup also

does the SQL server refer this backup set table every time it takes diff. backup.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 4

amarbreddy21 (2/11/2009)
Markus:
SQL Server records the first and last log sequence number (LSN) which is included in a backup
This LSN is stored only for backing up transaction logs r does it get
stored for Diff. backup also
does the SQL server refer this backup set table every time it takes diff. backup.

The LSN numbers are recorded fort all type of backups. Just have a look at the backupset table and you will see it. And yes all backups, Full, Diff or Log are registered in this table.

[font="Verdana"]Markus Bohse[/font]

amarbreddy21 SSC Veteran Points: 275 More actions · Answer 5

amarbreddy21

SSC Veteran

Points: 275

February 11, 2009 at 8:19 am

#942193

Thx MArk

amarbreddy21 SSC Veteran Points: 275 More actions · Answer 6

amarbreddy21

SSC Veteran

Points: 275

February 11, 2009 at 8:20 am

#942194

Thx Lowell that was help full

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 7

amarbreddy21 (2/11/2009)
How sql server understands the point at which the full backup is completed
how it recognizes and takes a differential backup from the point, the full backup has completed.
does it get stored into some table.

Hey,

This is achieve via an internal SQL Server page called Differential Change Map, which contains all the changed data file extents since the previous full backup. When an extent (a logical group of eight contiguous data file pages) is changed in any way, it is marked in a special bitmap page called the differential change map. There is a diff map for each 4GB chunk of each data file. When a differential backup is taken, the backup subsystem scans all the diff maps and copies all the changed extents, but the diff maps are not reset. This means that if more extents are changed between successive differential backups, the later backups will be larger. The diff maps are only reset when a full backup is performed.

Hope this helps.

Thanks,

Phillip Cox

SQL Server Consultant

MCITP - SQL Server 2008|DBAdmin

MCITP - SQL Server 2005|DBAdmin

MCTS - SQL Server 2008

MCTS - SQL Server 2005

MCP - SQL Server 2000