Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Differential Backups Internals

By Ken Kaufman,

One of the fundamental questions I ask perspective DBA candidates is: "Can you perform a differential backup under simple model?" This is a 50/50 chance of success, but yet the majority of candidates get this wrong. For those that get it right I follow up with "How does SQL make this possible?" Only one candidate has ever successfully answered this question. This article explains how differential backups occur under the covers, with the help of the dbcc page command.

If you're following along with this article, put your Adventureworks database in simple recovery mode, then perform a full backup for a clean start. Select a table and use the undocumented command dbcc ind command.

dbcc ind(adventureworks, [Production.TransactionHistory], 1)

What your looking at here is list of datapages for index 1, or the clustered index in this case. Explaining this command is outside of the scope of this paper but you want to find a row who's PageType = 1. This indicates the page is a data page (root) as compared to an intermediate index, or an IAM page. Find the corresponding PageFID (File ID), PagePid (Page Number) results in this row.

Once you grab these numbers, execute the undocumented command dbcc page. The Syntax is dbcc page (dbname/ID, FileNum, PageNum, 3 optional commands). For the case of this article put a '1' where the optional commands sit. In order to return results sets to Management Studio, turn on trace flag 3604 prior to running the command.

DBCC PAGE ('adventureworks', 1, 6608, 1)

Troll the results until you find "Diff" in the text. Notice that next to “Diff” it says "Not Changed". This tells you that this page had not been marked for a differential backup. Now go ahead and update the table, and run the command again.

update Production.TransactionHistory
set Quantity = 100
DBCC PAGE ('adventureworks', 1, 6608, 1)

The DIFF result now equals "Changed", obviously meaning the page is marked for a differential backup. Go ahead and run a differential backup and issue the DBCC command again.

Backup database adventureworks to disk = 'c:\testing\ad.diff'
with init, differential
DBCC PAGE ('adventureworks', 1, 6608, 1)

The output of this command is expected: the "Changed" value remains,  because only a full backup or a restore can change this. However I did prove that you can do a differential backup in simple mode. Run a full backup to set this back to "Not Changed". If you want to get a little wild, use the "copy_only" option of the backup, and look at the results.

Based on these results you would think that this value is in the data page is a form of a flag bit. This isn't the case at all. If this was the case a differential backup would require a full scan of every page in your database. You would end up doing almost the entire work of a full backup, without the disk writes. SQL Server gets around this by using a bitmask structure inside the sixth page of every data file called the DCM. It takes on a similar structure of the GAM and SGAM, by holding pointers to 64,000 extents or 4GB of data. As with the GAM every 4th GB a new DCM appears for tracking the following 4GB.

When a change to a data page occurs the DCM is updated for the changed extent in which the page lives, to indicate the change. When a differential backup is kicked off, rather than scanning all the pages in the database, it simply scans the DCM, which points to the needed data. The backup process then only grabs the changed extents, speeding up the process.

So if you happen to be interviewing at Zillow, and your asked if differential backups can occur in simple mode, the answer is yes.

Total article views: 4125 | Views in the last 30 days: 8
Related Articles

Differential Backup Prediction

SQL Server 2000 has a rock solid backup scheme , but do you really need to run full backups every da...


Differential Backup

Quick question on differential backup


Differential backup in 2005

Differential backup in 2005


Differential Backup Size

Differential Backup Size


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones