News for Differential Backup

SQL Server 2017 brings several improvements to us. Sometimes a simple new field in a DMV can turn possible interesting new functionalities. That’s what happens with differential backup in SQL Server 2017.

The DMV sys.dm_db_file_space_usage has a new field: modified_extent_page_count . This new field tell us how many pages were changed since the last full backup. Basically, it’s the information kept by DCM (Differential Change Map) that until now was very difficult to check.. We could use DBCC Page for this, however it’s not possible to use the information in automated scripts.

What are the possibilities with this new field ? We are now able to check how many extents have changed since last full backup and decide if a full backup is really needed or we can live with a differential backup, achieving smarter backup plans.

Change our full backup jobs to first check this field and decide if the backup will be full or differential can save space and maintenance time with databases that aren’t updated so often.

Let’s do a simple walkthrough to illustrate this. I will use AdventureWorks2016 database for this demonstration, you can download this sample database here: https://www.microsoft.com/en-us/download/details.aspx?id=49502

  1. Change the recovery model and take the first full backup:

  2. Check the modified pages

     

  3. Let’s do a lot of updates:

  4. Let’s check the modified pages again and run a simple backup script. There are not enough modified pages, the script will choose a differential backup.

  5. A lot more updates

  6. Again, check the modified pages and our backup script. Now there are a lot of modified extents, the script will choose a full backup.

  7. Check the modified pages again. The full backup cleaned the DCM.

  • 1490 views

  • Rate
    [Total: 2    Average: 4.5/5]