Tracy Boggiano continues her smart backups series, this time looking at differential backups:
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.
Click through for the script.