Deciding when to do a differential backup

SQL Server allows for differential backups, which only backups extends modified since the last full backup. The advantage of these differential backups is that if only a small portion of your data has changed since the last full backup, you need less time and storage for your backups! The disadvantage however is that the time needed for a restore increases: We must first restore the full backup, then restore the last differential backup.
So when few extends have changed since the last full backup, the gain on the backup is huge, and the pain when restoring is limited. But as more and more extends are being modified, the pain grows and the gain shrinks. So to decide whether we go for a full or differential backup we need to know the number of extends modified since the last full backup. But on SQL Server 2016 and earlier the only easy way to figure out was… by taking a differential backup.

SQL Server 2017 offers a nice improvement on this. In the sys.dm_db_file_space_usage dynamic management view an extra column modified_extent_page_count has been added which provides this information. So if you’re willing to stick to differential backups till they are 70% of the size of your full database, you could use this script to get the backup advice:

SELECT total_page_count, allocated_extent_page_count, modified_extent_page_count
, modified_extent_page_count * 100 / allocated_extent_page_count AS [% changed]
, CASE WHEN modified_extent_page_count * 100 / allocated_extent_page_count > 70
    THEN 'FULL'
    ELSE 'DIFFERENTIAL'
    END AS Advice
FROM sys.dm_db_file_space_usage

An example:

Capture

So with this we can improve our maintenance plan. Next step: convince Ola to include this in his maintenance script Smile