Basically, does a differential backup only take backups of altered pages, within a database.
The short answer is ‘yes’, but the detail is a little bit more interesting than that.
What is a Differential Backup?
This is a backup of all changes made since the last full backup. It is a cumulative backup – therefore only the latest is required, in addition to the latest full backup taken prior to that differential backup.
This differs from a log backup (known as an incremental backup in some circles), which only contains the changes since the previous log backup.
If you have weekly full backups, daily differential backups and hourly log backups, you could restore a full backup from three weeks ago and (if you still have them) every log backup from that point to the latest log backup. However, if you restore a full backup from three weeks ago and attempt to restore the latest differential backup, it will not work. The differential backup requires the latest full backup taken prior to the differential.
Firstly, we create the test database and a simple test table.
Listing 1: Create test database, table and insert test data
CREATE DATABASE DiffTest; GO USE [master]; GO ALTER DATABASE [DiffTest] SET RECOVERY FULL WITH NO_WAIT; GO USE DiffTest; GO CREATE TABLE dbo.Test ( ID INT NOT NULL IDENTITY, SomeText NVARCHAR(200) NULL ); GO INSERT INTO dbo.Test ( SomeText ) VALUES (N'Test Text'), (N'More Test Text'); GO
Having created the database and test data, we now need to complete a full backup. A differential backup (or indeed, a log backup) cannot be completed unless there has been a full backup beforehand.
This backup is going to my ‘C:\Temp’ folder. You may need to change this for your own environment.
Listing 2: Execute the First Full Backup
BACKUP DATABASE [DiffTest] TO DISK = N'C:\Temp\DiffTestFull.bak' WITH NOFORMAT, NOINIT, NAME = N'DiffTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM; GO
Looking at the Initial Data
Having completed a full backup and then done nothing else to the database, let us look at the number of changed extents. On thje face of it, you would expect no changes at this point.
Listing 3: Query the Number of Changed Extents Since the Last Backup
SELECT file_id, total_page_count, modified_extent_page_count, (100 * modified_extent_page_count) / total_page_count [percentage_changed] FROM sys.dm_db_file_space_usage; GO
Figure 1: Result from Listing 3
The figures that you see may be slightly different – I’ve had a variety of results when repeating these tests. Regardless of the values – data has changed. Some of this is because the database has to be updated as a result of the backup. For example, the backup Log Sequence Numbers (LSN) need to be updated.
Focusing on the Test Table
So, we shall look at the Test table directly, and check the DIFF flag for that page.
Firstly, we need to set the Trace Flag 3604, which sends DBCC output to the screen, instead of the SQL log.
Then, use DBCC IND to return details of the Test table, which will be used in a later call to DBCC PAGE.
Listing 4: Use DBCC IND
DBCC TRACEON(3604); GO DBCC IND(DiffTest, Test, -1); /*Look at page type of 1 and then use PageFID and PagePID for the following command*/ GO
In the results, where the PageType column is 1, we need the values of PageFID and PagePID. The values in your results may differ from those shown below.
Figure 2: Results from Listing 4
Using the values taken from Figure 2, look at the corresponding page.
Listing 5: DBCC PAGE Query for the Test table
DBCC PAGE(DiffTest, 1, 240, 3); GO
Figure 3: Result from Listing 5
The DIFF flag is set to ‘NOT CHANGED’ and the current row entry (‘Test Text’) can be seen.
We will now change the value of this row and check the page details again.
Listing 6: Update the Test Value and Check the Page Again
UPDATE dbo.Test SET SomeText = 'Changed Text' WHERE ID = 1; GO DBCC PAGE(DiffTest, 1, 240, 3); GO
Figure 4: Result from Listing 6
In the example above, the DIFF flag is now ‘CHANGED’, which is what the differential backup process checks for.
Run a Differential Backup and Check Again
So, having set the DIFF flag to changed, we will run a differential backup and check this page afterwards.
Listing 7: Run a Differential Backup
BACKUP DATABASE [DiffTest] TO DISK = N'C:\Temp\DiffTestFull.bak' WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'DiffTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM; GO
Having completed a differentail backup, check the DIFF flag on that page again.
Listing 8: Re-run DBCC PAGE
DBCC TRACEON(3604); GO DBCC PAGE(DiffTest, 1, 240, 3); GO
Figure 5: Result from Listing 8
Having completed a differential backup, the ‘DIFF’ flag is still set to ‘CHANGED’. This is of course, correct – as differential backups are cumulative. Therefore, every differential backup contains the changes since the last full backup – which includes the contents of each differential backup since that last full backup. The only action that resets the DIFF flag is a full backup.
So, run a full backup and check again.
Listing 9: Full Backup and DBCC PAGE
BACKUP DATABASE [DiffTest] TO DISK = N'C:\Temp\DiffTestFull.bak' WITH NOFORMAT, NOINIT, NAME = N'DiffTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, CHECKSUM; GO DBCC PAGE(DiffTest, 1, 240, 3); GO
Figure 6: Result from Listing 9
Now we can see that the DIFF flag has been reset to ‘NOT CHANGED’. Until this value changes, this page will no longer be part of a differential backup.
Rollback and the DIFF Flag.
So, if I update a row within a transaction, the entire transaction is reverted back to the original state. For more entertainment relating to transactions, read my post on explicit transactions.
Therefore, if I start a transaction, update a row and rollback the transaction, there has been no changes to that page. So the page will not be part of a differential backup, right?
Give it a go and see.
Listing 10: Rollback a Change and Check the DIFF Flag
DBCC TRACEON(3604); GO BEGIN TRANSACTION; UPDATE dbo.Test SET SomeText = 'Changed Text yet again' WHERE ID = 1; ROLLBACK TRANSACTION; GO DBCC PAGE(DiffTest, 1, 240, 3); GO
Figure 7: Result from Listing 10
So, in Figure 7, the DIFF flag is still set to ‘CHANGED’, even though a rollback reverted the UPDATE. Therefore, with no altered data, this page will now be part of a differential backup.
A rollback does not also roll back the setting of the DIFF flag. This makes sense, as there may be several changes being made from multiple transactions. Just because one has been rolled back it does not mean there are not other transactions working on this page. The work required, to ensure that this is the only transaction against this page and therefore can reset the DIFF flag, is probably more effort than it is worth, on Microsoft’s part.
Part of the reason that I enjoy writing these articles, is the additional things that I learn whilst making sure I can prove the contents of article that I’m writing. During my work on this article, I discovered another change that doesn’t roll back – page splits. If a transaction has caused a page split, then if the transaction is rolled back, the page split remains.
Yes, a differential backup does backup changed pages, but the reason the page has changed might not be because you’ve altered any data. Changes made during SQL Server’s housekeeping and restructuring can cause an altered page and once a page has been altered for those reasons, it will not roll back.