Notes on SQL

Random articles from a puzzled DBA

Background

Whilst watching a Pluralsight training course by Paul Randal, on ‘Logging, Recovery and the Transaction log’, a particular snippet related to differential backups piqued my curiosity.

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.

Examples

Preparation

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

7 percent of Changes

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

DBCC IND Result

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

Unchanged Page

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

Set the DIFF flag to CHANGED

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

No Change After Diff Backup

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

DIFF Flag re-set

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

Changed, Despite the Rollback

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.

Page Splits

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.

Conclusion

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.

References

SQL Server Trace Flags Complete List

Explicit Transactions

Wikipedia – Differential Backup

Page Splits Don’t Roll Back

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: