SQL Server 2017 – Smart Differential Backup

Hello Friends,

SQL Server 2017 CTP announced with lots of new features to help database professionals. One of that feature is Smart Differential Backup i.e. exposing the modified extent page counts. You can download the SQL Server 2017 in the fastest way as explained by Avanish in his recent post. In this blog post we will learn about the new feature of SQL Server 2017 for Smart Differential Backup.

There are some important points about differential backups:

1. Differential backup is always based on previous full backup. During the restore scenario, that full backup will work as a base.
2. Diferential backup contains the data that has been changed since most recent full backup.

Generally, For large size databases the most widely used backup process is like:

1. Full backup on Sunday midnight.
2. Differential backup at midnight everyday (Except Sunday).
3. Log backups based on recovery point objective. It might be every 15 minutes or 30 minutes.

As a database professional, we generally used differential backups to save space or to minimize the number of files to restore (in case of recovery) while using differential with log backups. We generally implement the above explained backup approach on the servers by using maintenance plan, TSQL scripts in Agent Jobs etc.

One important point to think here is the “Diferential backup contains the data that has been changed since most recent full backup”. If lots of data has been changed (lets say more than 80%-90%) since last full backup then differential backup will take almost same space as compare to full backup. In such scenario we can’t save good amount of storage which was one of the reson for implementing differential backup.

With SQL Server 2017 (CTP), a new column modified_extent_page_count has been introduced in sys.dm_db_file_space_usage dmv. Using this column, we can get the information about how much data has been changed since last full backup i.e. Amount of data need to be back up by differential backup.

Step 1: Create a database and table along with some data:

USE [Master]
GO
CREATE DATABASE FGTest;
GO
USE [FGTest]
GO
CREATE TABLE tbl_SelectIntoTest
(
RecordID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50),
LName VARCHAR(50),
City VARCHAR(100),
DeptID INT NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX CIX_tbl_SelectIntoTest_RecordID ON tbl_SelectIntoTest(RecordID)
GO

DECLARE @Counter INT;
SET @Counter = 1;

SET NOCOUNT ON;

WHILE (@Counter<=100000)
BEGIN
	IF(@Counter%4=1)
		INSERT INTO tbl_SelectIntoTest VALUES('Ahmad','Osama','Gurgaon',3);
	ELSE IF(@Counter%4=2)
		INSERT INTO tbl_SelectIntoTest VALUES('Prince','Rastogi','Noida',4);
	ELSE IF(@Counter%4=3)
		INSERT INTO tbl_SelectIntoTest VALUES('Avanish','Panchal','Gurgaon',7);
	ELSE
		INSERT INTO tbl_SelectIntoTest VALUES('Amit','Yadav','Delhi',4);

	SET @Counter = @Counter + 1;
END

USE [FGTEST]
GO
SELECT 
	DB_NAME(database_id) AS DBName, 
	File_ID, 
	total_page_count, 
	allocated_extent_page_count, 
	unallocated_extent_page_count, 
	mixed_extent_page_count, 
	modified_extent_page_count,
	CAST((modified_extent_page_count*100.0)/allocated_extent_page_count AS DECIMAL(9,2)) AS [DataChanged%age]
FROM sys.dm_db_file_space_usage

BACKUP DATABASE FGTEST to DISK='D:\FGTest.BAK';
GO

Step 2: Modified some data and check the DMV:

USE [FGTest]
GO
UPDATE Tbl_SelectIntoTest
	SET DeptID = 5
WHERE RecordID%4=0
GO
UPDATE Tbl_SelectIntoTest
	SET City = 'Gaziabad'
WHERE RecordID%4=1
GO

USE [FGTEST]
GO
SELECT 
	DB_NAME(database_id) AS DBName, 
	File_ID, 
	total_page_count, 
	allocated_extent_page_count, 
	unallocated_extent_page_count, 
	mixed_extent_page_count, 
	modified_extent_page_count,
	CAST((modified_extent_page_count*100.0)/allocated_extent_page_count AS DECIMAL(9,2)) AS [DataChanged%age]
FROM sys.dm_db_file_space_usage
GO

Step 3: Take Differential Backup, value (DataChanged%age) will not be changed:

USE [master]
GO
BACKUP DATABASE FGTEST
TO DISK='D:\FGTest.DIF'
WITH DIFFERENTIAL;
GO
USE [FGTEST]
GO
SELECT 
	DB_NAME(database_id) AS DBName, 
	File_ID, 
	total_page_count, 
	allocated_extent_page_count, 
	unallocated_extent_page_count, 
	mixed_extent_page_count, 
	modified_extent_page_count,
	CAST((modified_extent_page_count*100.0)/allocated_extent_page_count AS DECIMAL(9,2)) AS [DataChanged%age]
FROM sys.dm_db_file_space_usage
GO

Step 4: Take Full Backup, value will be changed:

USE [master]
GO
BACKUP DATABASE FGTEST
TO DISK='D:\FGTest_Second_Full.BAK';
GO
USE [FGTEST]
GO
SELECT 
	DB_NAME(database_id) AS DBName, 
	File_ID, 
	total_page_count, 
	allocated_extent_page_count, 
	unallocated_extent_page_count, 
	mixed_extent_page_count, 
	modified_extent_page_count,
	CAST((modified_extent_page_count*100.0)/allocated_extent_page_count AS DECIMAL(9,2)) AS [DataChanged%age]
FROM sys.dm_db_file_space_usage
GO

Size of differential and full backup (taken in above step 3 and 4) is as shown below. You can compare the size as well.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook