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.