SQL Server Looking into Differential Backups

I seem to be writing solely about Azure so to shake things up a bit I am going back to my “roots”. In SQL Server your differential backup is cumulative and NOT incremental and a differential will contain the data that has changed since the last full backup.

Let’s dig in using DBCC PAGE.

DBCC TRACEON (3604);
GO

DBCC PAGE (AdventureDB, 1, 762, 3);

This special allocation tracking page (DIFF map) is tracking what extents have been changed since the last full backup.

NotChanged

Assume I have taken a full back up as my base. I will then make changes.

   UPDATE [AdventureDB].[HumanResources].[Department]
  SET GroupName = 'Stark Labs'
  WHERE GroupName = 'Manufacturing'

  GO

 UPDATE [AdventureDB].[HumanResources].[Department]
 SET ModifiedDate = GETDATE()

  --Recheck
DBCC TRACEON (3604);
GO

DBCC PAGE (AdventureDB, 1, 762, 3);

Notice the changed status.

changed

So expect this change to be captured within my differential backup. The code for a differential backup looks like:

BACKUP DATABASE AdventureDB TO DISK = 'C:\SQLSERVER\AdventureDIFF.BAK'
WITH Differential

Expect this to be small – because I didn’t change much.

small

The DIFF map will get reset after a new full backup.

BACKUP DATABASE [AdventureDB] TO DISK = 'C:\SQLSERVER\AdventureFULL1.bak'

  --Recheck
DBCC TRACEON (3604);
GO

DBCC PAGE (AdventureDB, 1, 762, 3);

nochange

Your differential backups could get big; it just depends on the activity on your database. Look at the below screen shot.

set

Between FULL1 and DIFF2 I was issuing very big UPDATE statements.

 

Advertisements

4 thoughts on “SQL Server Looking into Differential Backups

  1. Thanks for the nice blog post. You can also see the degree of changes within a database by examining the modified_extent_page_count in sys.dm_db_file_space_usage.

  2. Pingback: Dew Drop – February 6, 2019 (#2893) | Morning Dew

Leave a Reply

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