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.