Implementing Smart Differential Backups Using Ola Hallengren’s Backup Scripts in SQL Server 2017

UPDATE, April 18, 2017: Ola has added this functionality directly to his scripts. Please download his scripts instead as they have more error handling and other functionality built-in.  I will be updating my configuration tables and procedures shortly.

This is part 2 of 2 on taking smart backups. I wrote a previous blog on how to use configuration tables to set up smart backups for transaction log backups.  This post we will see how to set up differential backups using the same configuration tables.

SQL Server 2017 introduced a new column for taking smarter backups for differential backups as part of the community-driven enhancements. A new column modified_extent_page_count is introduced in sys.dm_db_file_space_usage to track differential changes in each database file of the database.  The blog referenced states it takes just as many resources to take a differential backup as a full when there are between 70% and 80% of pages changes. With this field and the allocated_extent_page_count field, we can calculate the percentage of pages changed since the last full backup. So I have added logic into the differential backups that I use in combination with the configuration tables from my Github repository.  To support this change we will be adding two new fields to the DatabaseBackupConfig table:

• SmartBackup
• DiffChangePercent

The main part of the code determines if you are running SQL Server 2017 then determine which databases the percentage is greater than or equal to the value you put in the table.  Then it puts in two separate variables which databases to take full backups of and which ones to take differential backups of.

IF (@MajorVersion >= 14) AND (@SmartBackup = 'Y')
BEGIN
	CREATE TABLE #temp
	(	DatabaseName sysname NOT NULL,
		DiffChangePercent DECIMAL(5,2) NOT NULL
	)

	DECLARE @SQL NVARCHAR(MAX)
	DECLARE @DBFullBackups NVARCHAR(MAX)
	DECLARE @DBDiffBackups NVARCHAR(MAX)

	SELECT @SQL += REPLACE(REPLACE(
		 'SELECT DB_NAME(dsu.database_id) AS DBName, 
			CAST(ROUND((SUM(modified_extent_page_count) * 100.0) / SUM(allocated_extent_page_count), 2) AS DECIMAL(5,2)) AS "DiffChangePct"
		FROM sys.databases d
			CROSS APPLY {{DBName}}.sys.dm_db_file_space_usage dsu 
		GROUP BY dsu.database_id '
		,'{{DBName}}',d.name)
		,'"','''')
		FROM (
		SELECT d.name 
		FROM sys.databases d
		WHERE database_id > 4
	
	INSERT INTO #temp              
	EXEC sys.sp_executesql @SQL

	SELECT @DBFullBackups = COALESCE(@DBFullBackups + ',','') + DatabaseName 
	FROM #temp
	WHERE DiffChangePercent >= @DiffChangePercent 

	SELECT @DBDiffBackups = COALESCE(@DBDiffBackups + ',','') + DatabaseName
	FROM #temp
	WHERE DiffChangePercent < @DiffChangePercent 

	DROP TABLE #temp
END
ELSE
BEGIN
	SELECT @DBInclude = @Databases
END

From there are IFs in the job to decide whether to take a FULL or DIFF backup using Ola’s script and the parameters in the table for each. The full code is available in the GitHub repository linked above.