SQL Server 2017 Differential Backup Changes

By:   |   Comments (9)   |   Related: > SQL Server 2017


Problem

Differential backups play an important role in the backup policy for SQL Server, especially for large databases. Sometimes we observe that differential backups take a lot of time and it is almost equal in time and size as the full backup. We cannot determine how much data has changed since the last full backup to determine if we should take a Full or Differential backup.  In this tip, we will see how SQL Server 2017 solves this problem.

Solution

Before we start with the changes in SQL Server 2017 for differential backups, let me first give a brief introduction to SQL Server pages and extent allocations. This will help with understanding how differential backups work.

  • Page: A page is the most basic element of storage in SQL Server. The size of a page is 8KB.
  • Extent: An extent is a set of 8 contiguous data pages, so it is 8 * 8KB = 64KB in size.

SQL Server Differential Backup Background

Differential backups backup all extents that have changed since the last full backup. Each time any data has changed a flag is turned on to let SQL Server know that if a "Differential" backup is created it should include the data from this extent. When a "Full" backup is taken these flags are reset. The Differential Change Map (DCM) is used by SQL Server to backup only these extents during a differential backup.  If the bit for a particular extent is on in its DCM page, at least one page within that extent has changed since the last full backup.

Creating a SQL Server Differential Backup

Let's create a database and table then insert some records into the table.

CREATE DATABASE MSSQLTIPS
GO

USE MSSQLTIPS
GO

CREATE TABLE [dbo].[TestTable]
   (
   [ID] [int] NULL,
   [Name] [nchar](10) NULL
   ) ON [PRIMARY]
GO

INSERT INTO TestTable VALUES(1,'A')
INSERT INTO TestTable VALUES(2,'B')
GO

Now let's perform a full backup, so we can then create differential backups.

BACKUP DATABASE [MSSQLTIPS] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLVNEXT\MSSQL\Backup\MSSQLTIPS.bak'
WITH NOFORMAT, NOINIT, NAME = N'MSSQLTIPS-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
SQL Server database full backup

Now we will enable trace flag 3604 and review the DBCC page output to see what extents have changed.  If we run sp_helpdb we can see the database id or dbid for all databases.  In our case, the MSSQLTIPS database id is 5, which we will use for DBCC PAGE. Here is more information about DBCC PAGE and the parameters. 

So for the DBCC PAGE command we are looking at:

  • database id 5
  • file number 1
  • page number 6 - this will show us the DCM page
  • with output option 3.
DBCC TRACEON(3604)--after full backup
DBCC PAGE(5,1,6,3)

In the output, we can see some of the extents show as CHANGED. We will not find a time when all extents have a status as NOT CHANGED since SQL Server writes backup history and other information in the extents.

DBCC Page output after Full backup

Now let's apply a few changes (inserts) to the table.

INSERT INTO TestTable VALUES(3,'C')
GO 100000

Let's review the DBCC page output again.

DBCC Page output after full backup and changes to the table

Due to our insert operation after the Full backup, there are many new extents allocated showing a status as "Changed."

Let's take the first differential backup now.

BACKUP DATABASE [MSSQLTIPS] 
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLVNEXT\MSSQL\Backup\MSSQL_Diff.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'MSSQLTIPS-Full Database Backup', SKIP, NOREWIND, 
NOUNLOAD, STATS = 10
GO

We can check the differential backup LSN with the below query. The differential backup LSN changes when we execute a full backup.

SELECT 
   d.name as DatabaseName,
   mf.name as DatabaseFileName,
   mf.differential_base_lsn,
   mf.differential_base_guid,
   mf.differential_base_time
FROM sys.master_files mf
JOIN sys.databases d on mf.database_id = d.database_id
WHERE mf.database_id = 5 -- just use the MSSQLTIPS databases.
AND mf.file_id <> '2' -- Exclude log files.
Differential backup base LSN

Now we will make more changes to the data in the database and look at the DBCC page output again.

UPDATE TestTable SET [name]='CCCC' WHERE [ID]=3
GO

DBCC TRACEON(3604)
GO

DBCC PAGE(5,1,6,3)

Below is the DBCC page output which shows new extents as well as some old extents with a status of "Changed".

DBCC Page output showing extents that have changed

The differential backup LSN is still the same as shown below and in the image above.

Differential Base LSN in SQL Server

As we have seen, we can get information from the DCM pages to see which database pages have changed for the differential backup. However it is difficult to use the information in automated scripts for earlier versions of SQL Server.

Changes in SQL Server 2017 Differential Backups

The SQL Server 2017 DMV sys.dm_db_file_space_usage has a new field i.e. modified_extent_page_count. This new field tells us how many pages were changed since the last full backup.  The modified page count can be used to track differential changes in the database since the last full backup to decide if a differential backup is beneficial.

Now let's run the DMV to see how many pages have been modified in our example.

SELECT 
   file_id,total_page_count, 
   modified_extent_page_count
FROM sys.dm_db_file_space_usage
SQL Server 2017 DMV sys.dm_db_file_space_usage has a new field i.e. modified_extent_page_count

We see out of total page count of 1024 that 592 have been modified. 

We can also include the percentage change to the database as shown below.

SELECT 
   file_id,total_page_count, 
   modified_extent_page_count, 
   (100 * modified_extent_page_count)/total_page_count [percent_changed] 
FROM sys.dm_db_file_space_usage
percentage change to the SQL Server database extents

Now let's perform some more changes to the database.

UPDATE TestTable SET [name]='EEE' WHERE [ID]=3
UPDATE TestTable SET [name]='EEE' WHERE [ID]=1
UPDATE TestTable SET [name]='FFF' WHERE [ID]=2
INSERT INTO TestTable VALUES(4,'F')
GO 10000
percentage change to the SQL Server database extents after update and insert commands

Now we can see that the database changes are 61%. We can use this information to see if we want a Differential backup or a Full database backup. Previously we didn't have this much control over the differential backup.

Now we can check this and determine if it makes sense to create a differential backup.  For a large database it is actually very beneficial where we sometimes observe that a differential backup is taking very long or almost as much time as a full backup.

Suppose I want to take a full database backup if changes are more than 50% otherwise a differential backup. We can use the following query.

CREATE or ALTER PROCEDURE DBbackup @threshold int
as
DECLARE @DBchanges Numeric(10,0)

SELECT @DBchanges = ( 100 * Sum(modified_extent_page_count) / Sum(total_page_count) ) 
FROM sys.dm_db_file_space_usage 

IF @DBchanges > @threshold 
  BEGIN 
     --Threshold exceeded, take a full backup 
     Print 'Threshold exceeded, take a full backup'
     BACKUP DATABASE MSSQLTIPS TO DISK='C:\mssqltips\Linux\Differential Backup Changes SQL Server 2017\MSSQLTIPS.bak' 
  END 
  ELSE 
  BEGIN 
      -- Threshold not exceeded, take a full backup , do a differential backup 
      Print 'Threshold not exceeded, take a Differential backup'
      BACKUP DATABASE MSSQLTIPS TO DISK='C:\mssqltips\Linux\Differential Backup Changes SQL Server 2017\MSSQLTIPS_Diff.bak' 
      WITH differential 
   END
SQL Server stored procedure for full or differential backup

To run the stored procedure, pass the threshold value.  A value greater than this will create a Full backup otherwise a Differential backup will be created.

EXEC DBbackup @threshold= 50

For our database, a full backup is created since the percentage is 61% as shown in the above screenshot.

Full SQL Server database backup executed

After a full backup the DCM is reset, so if we observe the output of the DMV again we can see the DCM value is now down to 3%. Please note that after every backup, SQL Server stores information about that backup in the database which causes some pages to change right away, so we will never see a completely empty DCM.

Percentage Change after full backup is executed

Since we have a percentage changed of only 3 which below the threshold we are using, the stored procedure should execute a differential backup.

Differential SQL Server database backup executed

This is a very interesting and useful enhancement to SQL Server differential backups. Explore this and make use of it in your environment once SQL Server 2017 is launched.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, September 8, 2021 - 11:32:44 AM - Angel Cid Back To Top (89216)
Hi, i have SQL Server 2017 Enterprise Edition in Allways On config.

I have a scheduled job every day to make a full backup at 3am and differential backups every hour.

In one database, the diff backup is almost the same size (27gb) as the full backup, even if a full backup has been made the same day an hour before.

I have been making tests making a full backup and immediatly making the differential backup and the size of the backups remains the same.

This only happens with one database, the other databases diff backups are small comparing to the full backup.

Friday, September 8, 2017 - 9:53:00 PM - rajendra gupta Back To Top (66073)

 Hi Ron ,thanks for finding article useful and relevant. Since SQL 2017 final version is still not launched, there might be further updates, clarification as we move on. Overall idea is great of knowing changes since last full backups.will update here if u get more information regarding this.

 

Thanks

Rajendra

 

 

Friday, September 8, 2017 - 1:52:09 PM - Ron Wright Back To Top (66057)

 Isn't the modified_extent_page_count a little misleading.  Sure it tells us how many pages have been modified, but not how many extents.  Since a differential backup backs up modified extents, what we really need is a modified_extent_count and a total_extent_count (although this can be calculated from total_page_count).

Consider the scenario of a database file with 1024 pages (128 extents).  If two pages in every extent are modified, the resulting modified_extent_page_count value will be 256.  This will lead us to the conclusion that the database file is only 25% modified when in reality all of the extents have been modified leading to a differential backup which will include 100% of the database file.  If I use a threshold of 50%, I will be fooled into thinking that it would be best to do a differential when in fact a full backup is better.

I have already considered using the technique you outlined, thinking that it was a good one.  But now, given your explanation, I am not sure that modified_extent_page_count is all that useful.

Your article however, is very useful in that it explains all of the relavent details.  Thank you for that.

 

 

Thursday, September 7, 2017 - 1:47:18 PM - Randy in Marin Back To Top (66013)

Hi Rajendra, 

Many years ago, I filled a bug with VEEAM re this issue.  Sometime later I realized it's not really VEEAM, but a SQL Server issue.  I eventually filled a SQL suggestion.  

https://connect.microsoft.com/SQLServer/feedbackdetail/view/910349/protect-us-from-veeam#tabs 

In response to your request, I did a quick look at 3 SQL 2016 servers we just setup.  I took a look at the backup history.  There is improvement, maybe.  Copy-only snapshots are being used on two of the servers for virtual full backups.  For these, a system backup that follows a database full database backup will not prevent differential database backups.  For the other server, copy-only is not being used.  A system backup will cause a differential database backups to fail unless a new full db backup is done.  I will have to ask what backup products are in use.  (I don't know if the backup product is using a VSS copy-only snapshot.  If it did not, it must be able to request SQL to respond as if it did so - this would be ideal.) 

My point about a "bug" is that SQL Server should do what's right for SQL when responding to external system backups.  The system backup should use any sort of VSS snapshot it needs (copy-only or non-copy-only) to maintain the system backup chain.  The SQL response should only be to insure the database files in the system backup image will be valid without regard to the type of snapshot.  The system backup chain and a database backup chains are separate and unrelated.  So, I see no point in a SQL "virtual" backup ever being anything other than copy-only.  At the very least, it should be an option to set.  

My point about a "maybe" for improvement is that if the system backup must use a copy-only snapshot to get a copy-only response from SQL, then it's still broken.  I don't want the system backup to use a copy-only unless it's really the right thing to do for system backups.  Perhaps the backup software tracks differential changes in some other way, in which case it's fine.  But still, why does SQL allow backup software to break the chain when it's not a real database backup?  

Perhaps the system backup software supports point in time database restores.  In that case, then perhaps the database backup features in SQL Server need to be disabled.  Or perhaps SQL Server can maintain two backup chains - one for DPM and one for DBAs.  In either case, the proper response to a vss snapshot by SQL is a separate issue.  I think the DBA who is responsible for PIT restores owns the backup chain.   

Here is the T-SQL to look at the backups.  I will see "virtual" records in response to a system backup.   

select top 200 bs.server_name + '.' +  bs.database_name as [database],
 bs.user_name,
 bs.backup_start_date,
 bs.backup_finish_date,
 CASE bs.type WHEN 'D' THEN 'D = Database'
  WHEN 'I' THEN 'I = Differential database' WHEN 'L' THEN 'L = Log'
     WHEN 'F' THEN 'F = File or filegroup'
  WHEN 'G' THEN 'G =Differential file' WHEN 'P' THEN 'P = Partial'
  WHEN 'Q' THEN 'Q = Differential partial'
  ELSE CAST(bs.type as char(1))
  END as [type],
    bs.recovery_model,
 bs.is_snapshot as [snapshot],
 bs.is_copy_only as [copy_only],
 bf.physical_device_name,
 CASE bf.device_type WHEN 2 THEN '2 = Disk'
  WHEN 5 THEN '5 = Tape'
  WHEN 7 THEN '7 = Virtual device'
  ELSE CAST(bf.device_type as varchar(4))
  END as [device_type]
from msdb.dbo.backupset bs
join msdb.dbo.[backupmediafamily] bf
on bs.media_set_id = bf.media_set_id
where 1=1 
order by backup_start_date desc

Thanks,
Randy

 

Thursday, September 7, 2017 - 8:48:56 AM - rajendra gupta Back To Top (65984)

 Hi Randy

 

Could you pls provide more details of your issues with backups.

 

Thanks

Rajendra

 

Thursday, September 7, 2017 - 8:36:46 AM - Jeremy Kadlec Back To Top (65981)

Andriy,

Thank you for the post.  We have checked out the URL you provided and updated the tip.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, September 6, 2017 - 1:37:54 PM - Randy in Marin Back To Top (65947)

Thanks.  Do you know if there are any updates regarding the effect of a system backup (e.g., veeam, DPM, etc.) on differential backups?  Our SQL servers respond to the VSS snapshot using a normal full database backup, not a copy-only backup.  This affects the backup chain and our differentials fail because there is no valid last backup.  (SQL detects that the last full backup is virtual which is not valid for a new differential backup.)  If it's not a valid full backup, then SQL Server should not treated it as valid and do a copy-only full backup instead.  This is a bug, not a feature.  

Wednesday, September 6, 2017 - 3:34:12 AM - Andriy Pogorelov Back To Top (65924)

Hi, you wrote: 

" As we have seen, we can get information from the DCM pages to see which database pages have changed for the differential backup. However it’s not possible to use the information in automated scripts."

^^^

that's an incorrect statement. it's absolutely possible. take a look here:

https://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/

 

Tuesday, September 5, 2017 - 11:30:00 AM - Harit Patel Back To Top (65904)

Excellent article with very clear examples.














get free sql tips
agree to terms