{"id":71186,"date":"2017-06-02T02:25:35","date_gmt":"2017-06-02T02:25:35","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71186"},"modified":"2021-08-24T13:39:24","modified_gmt":"2021-08-24T13:39:24","slug":"news-differential-backup","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/news-differential-backup\/","title":{"rendered":"News for Differential Backup"},"content":{"rendered":"

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.<\/p>\n

The DMV sys.dm_db_file_space_usage has a new field: modified_extent_page_count . This new field tell us how many pages\u00a0were 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..\u00a0We could use DBCC Page for this, however it’s not possible to use the information in automated scripts.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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<\/a><\/p>\n

    \n
  1. \n

    Change the recovery model and take the first full backup:<\/p>\n

    use AdventureWorks2016CTP3\r\ngo\r\nAlter database AdventureWorks2016CTP3 set recovery full\r\ngo\r\n\u2014 The initial full backup\r\nbackup database adventureworks2016ctp3\r\nto disk=\u2018c:\\backups\\newCopy.bak\u2019<\/pre>\n
  2. \n
    Check the modified pages<\/p>\n
    \r\n\u2014 Identifying the amount of changes \r\nSelect file_id,total_page_count, modified_extent_page_count, \r\n       (100 * modified_extent_page_count)\/total_page_count [percent] \r\nfrom sys.dm_db_file_space_usage<\/pre>\n
    \u00a0<\/p>\n<\/li>\n
  3. \n
    Let’s do a lot of updates:<\/p>\n
    \r\n\u2014 Let\u2019s do a lot of updates \r\nupdate sales.SalesOrderDetail \r\nset orderqty=orderqty+1\r\n \r\nupdate sales.ordertracking \r\nset eventdetails=eventdetails + \u2018x\u2019\r\n \r\nupdate production.TransactionHistory \r\nset quantity=quantity +1\r\n \r\nupdate production.TransactionHistoryArchive \r\nset quantity=quantity +1\r\n \r\nupdate sales.SalesOrderDetail_ondisk \r\nset OrderQty= OrderQty +1\r\n \r\nupdate person.person \r\nset emailpromotion=1<\/pre>\n<\/li>\n
  4. \n
    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.<\/p>\n
    \r\n\u2014 Identifying the amount of changes \u2013 again \r\nSELECT file_id, \r\n       total_page_count, \r\n       modified_extent_page_count, \r\n       ( 100 * modified_extent_page_count ) \/ total_page_count [percent] \r\nFROM   sys.dm_db_file_space_usage<\/pre>\n
    <\/p>\n
    \r\n\u2014 Our backup script will be like this \r\nDECLARE @changes NUMERIC(15, 3) \r\nSELECT @changes = ( 100 * Sum(modified_extent_page_count) \/ \r\n                    Sum(total_page_count) ) \r\nFROM   sys.dm_db_file_space_usage \r\nIF @changes > 65 \r\n  BEGIN \r\n      \u2014 Too many changes, do a full backup \r\n      BACKUP DATABASE adventureworks2016ctp3 TO DISK=\u2018c:\\backups\\newCopy.bak\u2019 \r\n  END \r\nELSE \r\n  BEGIN \r\n      \u2014 Too few changes, do a differential backup \r\n      BACKUP DATABASE adventureworks2016ctp3 TO DISK=\u2018c:\\backups\\newCopy.bak\u2019 \r\n      WITH \r\n      differential \r\n  END <\/pre>\n
    <\/p>\n<\/li>\n
  5. \n
    A lot more updates<\/p>\n
    \r\n\u2014 Let\u2019s do a lot more updates \r\nUPDATE sales.customerpii \r\nSET    lastname = lastname + \u2018x\u2019 \r\nUPDATE production.workorderrouting \r\nSET    actualcost = actualcost + 1 \r\nUPDATE sales.salesorderheader \r\nSET    subtotal = subtotal + 1 \r\nUPDATE sales.salesorder_json \r\nSET    subtotal = subtotal + 1 \r\nUPDATE person.address \r\nSET    addressline1 = addressline1 + \u2018x\u2019 \r\nUPDATE person.emailaddress \r\nSET    emailaddress = emailaddress + \u2018x\u2019 <\/pre>\n<\/li>\n
  6. \n
    Again, check the modified pages and our backup script. Now there are a lot of modified extents, the script will choose a full backup.<\/p>\n
    \r\n\u2014 Identifying the amount of changes \u2013 again \r\nSELECT file_id, \r\n       total_page_count, \r\n       modified_extent_page_count, \r\n       ( 100 * modified_extent_page_count ) \/ total_page_count [percent] \r\nFROM   sys.dm_db_file_space_usage<\/pre>\n
    <\/p>\n
    \r\n\u2014 Our backup script will be like this \r\nDECLARE @changes NUMERIC(15, 3) \r\nSELECT @changes = ( 100 * Sum(modified_extent_page_count) \/ \r\n                    Sum(total_page_count) ) \r\nFROM   sys.dm_db_file_space_usage \r\nIF @changes > 65 \r\n  BEGIN \r\n      \u2014 Too many changes, do a full backup \r\n      BACKUP DATABASE adventureworks2016ctp3 TO DISK=\u2018c:\\backups\\newCopy.bak\u2019 \r\n  END \r\nELSE \r\n  BEGIN \r\n      \u2014 Too few changes, do a differential backup \r\n      BACKUP DATABASE adventureworks2016ctp3 TO DISK=\u2018c:\\backups\\newCopy.bak\u2019 \r\n      WITH differential \r\n  END <\/pre>\n
    <\/p>\n<\/li>\n
  7. \n
    Check the modified pages again. The full backup cleaned the DCM.<\/p>\n
    \r\n\u2014 Identifying the amount of changes \u2013 again \r\nSELECT file_id, \r\n       total_page_count, \r\n       modified_extent_page_count, \r\n       ( 100 * modified_extent_page_count ) \/ total_page_count [percent] \r\nFROM   sys.dm_db_file_space_usage <\/pre>\n
    <\/p>\n<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    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\u00a0were changed since the last full backup….…<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143527],"tags":[],"coauthors":[6810],"class_list":["post-71186","post","type-post","status-publish","format-standard","hentry","category-blogs","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71186","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71186"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71186\/revisions"}],"predecessor-version":[{"id":92194,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71186\/revisions\/92194"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71186"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71186"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71186"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71186"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}