Smart Differential Backups

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.

Related Posts

User-Defined Restore Points In Azure SQL DW

Kevin Ngo announces a new feature in Azure SQL Data Warehouse: Previously, SQL DW supported only automated snapshots guaranteeing an eight-hour recovery point objective (RPO). While this snapshot policy provided high levels of protection, customers asked for more control over restore points to enable more efficient data warehouse management capabilities leading to quicker times of […]

Read More

Restoring Point-In-Time To Another Azure SQL Managed Instance

Jovan Popovic announces an improvement to Azure SQL Database Managed Instances: Azure SQL Database Managed Instance enables you to create a database as a copy of another database at some point in time in the past. This is known as point-in-time restore feature, and up till now you could perform point-in-time restore only within the same instance. […]

Read More

Categories

January 2018
MTWTFSS
« Dec Feb »
1234567
891011121314
15161718192021
22232425262728
293031