{"id":5178,"date":"2021-01-18T14:29:24","date_gmt":"2021-01-18T22:29:24","guid":{"rendered":"https:\/\/www.sqlskills.com\/blogs\/paul\/?p=5178"},"modified":"2021-01-30T15:33:07","modified_gmt":"2021-01-30T23:33:07","slug":"the-curious-case-of-the-failing-differential-restore","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/","title":{"rendered":"The Curious Case of&#8230; the failing differential restore"},"content":{"rendered":"<p style=\"text-align: justify;\"><em>(The Curious Case of\u2026<\/em>\u00a0used to be part of our bi-weekly\u00a0<a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">newsletter<\/a>\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)<\/p>\n<p style=\"text-align: justify;\">First blog post of the year!<\/p>\n<p style=\"text-align: justify;\">This is an issue that&#8217;s come up several times over the last few weeks: restore of a differential backup failing while initializing a log shipping secondary.<\/p>\n<p style=\"text-align: justify;\">In one case, the database is over a terabyte and has a lot of changes being made. The idea was to copy and restore the full backup, perform a differential backup and restore it too, and then have a small amount to catch up on with log backups before starting log shipping for real. The problem was that the differential restore was consistently failing with this error:<\/p>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 3136, Level 16, State 1, Line 75\r\nThis differential backup cannot be restored because the database has not been restored to the correct earlier state.\r\nMsg 3013, Level 16, State 1, Line 75\r\nRESTORE DATABASE is terminating abnormally.\r\n<\/pre>\n<p style=\"text-align: justify;\">After going back and forth a few times in email, it turned out that they\u2019d used the <em>COPY_ONLY<\/em> option on the full backup they performed to initialize the secondary. They mistakenly thought they had to do that to avoid messing up the log backup chain on their production system, which is incorrect as full backups have no effect on the log backup chain at all.<\/p>\n<p style=\"text-align: justify;\">When you specify <em>COPY_ONLY<\/em> with a full backup, the full backup process does not reset the differential bitmaps in the data files, and this means it cannot be used as the basis for any restore sequence involving a differential backup. The <em>COPY_ONLY<\/em> backup gets a different <em>&#8216;differential base GUID&#8217;<\/em> which will not match any subsequent differential backups &#8211; i.e. it cannot be used as the base for restoring a differential.<\/p>\n<p style=\"text-align: justify;\">If you perform \u00a0the following sequence of operations:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Normal full backup<\/li>\n<li>Copy-only full backup<\/li>\n<li>Differential backup<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">And then do a <em>RESTORE HEADERONLY<\/em> on all three backups, you\u2019ll notice that the <em>BackupSetGUID<\/em> values for the two full backups are different, and that the <em>DifferentialBaseGUID<\/em> for the differential backup matches the <em>BackupSetGUID<\/em> for the first full backup.<\/p>\n<p style=\"text-align: justify;\">Bottom line: if you\u2019re going to use <em>COPY_ONLY<\/em> for any backup operation, make sure you understand the ramifications of doing so.<\/p>\n<p>PS One of the commenters taught me something &#8211; if you restore the first log backup performed after the copy-only full backup, you can then restore the differential and it doesn&#8217;t complain!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) First blog post [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,115],"tags":[],"class_list":["post-5178","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-the-curious-case-of"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>The Curious Case of... the failing differential restore - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Curious Case of... the failing differential restore - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) First blog post [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2021-01-18T22:29:24+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-01-30T23:33:07+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\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:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/\",\"name\":\"The Curious Case of... the failing differential restore - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2021-01-18T22:29:24+00:00\",\"dateModified\":\"2021-01-30T23:33:07+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Curious Case of&#8230; the failing differential restore\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Curious Case of... the failing differential restore - Paul S. Randal","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:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/","og_locale":"en_US","og_type":"article","og_title":"The Curious Case of... the failing differential restore - Paul S. Randal","og_description":"(The Curious Case of\u2026\u00a0used to be part of our bi-weekly\u00a0newsletter\u00a0but we decided to make it a\u00a0regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.) First blog post [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/","og_site_name":"Paul S. Randal","article_published_time":"2021-01-18T22:29:24+00:00","article_modified_time":"2021-01-30T23:33:07+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/","name":"The Curious Case of... the failing differential restore - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2021-01-18T22:29:24+00:00","dateModified":"2021-01-30T23:33:07+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-curious-case-of-the-failing-differential-restore\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Curious Case of&#8230; the failing differential restore"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5178","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=5178"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/5178\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=5178"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=5178"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=5178"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}