{"id":2667,"date":"2015-10-28T06:24:53","date_gmt":"2015-10-28T10:24:53","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2667"},"modified":"2023-10-17T05:41:11","modified_gmt":"2023-10-17T09:41:11","slug":"differential-backup","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/differential-backup\/","title":{"rendered":"Differential Backup"},"content":{"rendered":"<p>A differential backup is created similarly to a full backup, but\u00a0with one important difference &#8211; the differential backup only contains the data that has changed since the last full backup (the active portion of the transaction log).<!--more--> Differential backups are cumulative not incremental. This means that a differential backup contains all changes made since the last full backup, in spite of the fact that\u00a0they have already been included in previous differential backups. Differential backups are created the following way:<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-2779 size-full\" src=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Differential-backup.png\" alt=\"Differential backup\" width=\"648\" height=\"300\" srcset=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Differential-backup.png 648w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Differential-backup-300x139.png 300w\" sizes=\"(max-width: 648px) 100vw, 648px\" \/><\/p>\n<p>&nbsp;<\/p>\n<h2>How Differential Backups\u00a0Work<\/h2>\n<p>Every 4GB section (GAM interval) of data file has a special database page that is called differential bitmap. It tracks to which extent\u00a0(i.e. eight physically contiguous pages) any data has changed since the last full backup. The differential backup reads all bitmaps and looks for the data file extents that are marked as changed in order to backup them.<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-2780 size-full\" src=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Data-extents.png\" alt=\"Data extents\" width=\"648\" height=\"320\" srcset=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Data-extents.png 648w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Data-extents-300x148.png 300w\" sizes=\"(max-width: 648px) 100vw, 648px\" \/><\/p>\n<p>The size of a differential backup depends directly on the amount\u00a0of data\u00a0that has changed. The more changes are marked in the differential bitmaps the bigger next differential backup will be. Ultimately the differential database backup can become as large as the full backup. Therefore, it is recommended to make the next full backup regularly to reset all bitmaps in the differential bitmap, thus zeroizing the differential backup size.<\/p>\n<h2>Differential Backup Chain<\/h2>\n<p>Every differential backup has its own LSN chain and each full backup resets the differential base LSN. Therefore, to make an extra (unscheduled) full database backup you need to use T-SQL option &#8220;WITH COPY_ONLY&#8221;, otherwise the differential backup chain will be broken and all subsequent differential backups will follow the new full backup, instead of scheduled full database backups.<\/p>\n<pre><span style=\"color: #0000ff;\">BACKUP DATABASE<\/span> <em>your_database<\/em> <span style=\"color: #0000ff;\">TO DISK<\/span> = <span style=\"color: #ff0000;\">'full.bak'<\/span> <span style=\"color: #0000ff;\">WITH COPY_ONLY<\/span><\/pre>\n<h2>How to Make a Differential Backup<\/h2>\n<p>To make a\u00a0differential database backup simply add <strong>&#8220;WITH DIFFERENTIAL&#8221;\u00a0<\/strong>clause:<\/p>\n<pre><span style=\"color: #0000ff;\">BACKUP DATABASE<\/span> <em>your_database<\/em> <span style=\"color: #0000ff;\">TO<\/span> <span style=\"color: #0000ff;\">DISK<\/span> = <span style=\"color: #ff0000;\">'diff.bak'<\/span> <span style=\"color: #0000ff;\">WITH DIFFERENTIAL<\/span><\/pre>\n<p>Using\u00a0<strong>SQL Server Management Studio (SSMS) <\/strong>is another way of creating differential backups: right click on the database you want to backup, select &#8220;Tasks&#8221;, then &#8220;Back up&#8230;&#8221;. Choose &#8220;Differential&#8221; type, add backup destination and click &#8220;OK&#8221;.<\/p>\n<p>If you prefer third-party utilities\u00a0you can also use\u00a0<strong><a href=\"http:\/\/sqlbackupandftp.com\" target=\"_blank\" rel=\"noopener nofollow\">SQLBackupAndFTP<\/a><\/strong>\u00a0to create regular full and differential backups.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A differential backup is created similarly to a full backup, but\u00a0with one important difference &#8211; the differential backup only contains the data that has changed since the last full backup (the active portion of the transaction log).<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[156],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Differential Backup - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Differential Backup\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/academy.sqlbak.com\/differential-backup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Differential Backup - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Differential Backup\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/differential-backup\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-10-28T10:24:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:41:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Differential-backup.png\" \/>\n<meta name=\"author\" content=\"Alexandr Omelchenko\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Alexandr Omelchenko\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/differential-backup\/\",\"url\":\"https:\/\/academy.sqlbak.com\/differential-backup\/\",\"name\":\"Differential Backup - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-10-28T10:24:53+00:00\",\"dateModified\":\"2023-10-17T09:41:11+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Differential Backup\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/differential-backup\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/differential-backup\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/differential-backup\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Differential Backup\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/academy.sqlbak.com\/#website\",\"url\":\"https:\/\/academy.sqlbak.com\/\",\"name\":\"Sql Server Backup Academy\",\"description\":\"All you need to know about Sql Server database backup\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/academy.sqlbak.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\",\"name\":\"Alexandr Omelchenko\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g\",\"caption\":\"Alexandr Omelchenko\"},\"sameAs\":[\"http:\/\/sqlbak.com\"],\"url\":\"https:\/\/academy.sqlbak.com\/author\/alexandr\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Differential Backup - Sql Server Backup Academy","description":"Learn about SQL Server Differential Backup","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/academy.sqlbak.com\/differential-backup\/","og_locale":"en_US","og_type":"article","og_title":"Differential Backup - Sql Server Backup Academy","og_description":"Learn about SQL Server Differential Backup","og_url":"https:\/\/academy.sqlbak.com\/differential-backup\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-10-28T10:24:53+00:00","article_modified_time":"2023-10-17T09:41:11+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/10\/Differential-backup.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/differential-backup\/","url":"https:\/\/academy.sqlbak.com\/differential-backup\/","name":"Differential Backup - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-10-28T10:24:53+00:00","dateModified":"2023-10-17T09:41:11+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Differential Backup","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/differential-backup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/differential-backup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/differential-backup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Differential Backup"}]},{"@type":"WebSite","@id":"https:\/\/academy.sqlbak.com\/#website","url":"https:\/\/academy.sqlbak.com\/","name":"Sql Server Backup Academy","description":"All you need to know about Sql Server database backup","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/academy.sqlbak.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8","name":"Alexandr Omelchenko","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/8155f02a1f0f4ef52a4a68ef379a922f?s=96&d=monsterid&r=g","caption":"Alexandr Omelchenko"},"sameAs":["http:\/\/sqlbak.com"],"url":"https:\/\/academy.sqlbak.com\/author\/alexandr\/"}]}},"_links":{"self":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2667"}],"collection":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/comments?post=2667"}],"version-history":[{"count":29,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2667\/revisions"}],"predecessor-version":[{"id":3879,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2667\/revisions\/3879"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}