{"id":710,"date":"2010-04-20T13:24:00","date_gmt":"2010-04-20T13:24:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(2030)-restarting-a-log-backup-chain-requires-a-full-backup.aspx"},"modified":"2017-07-24T09:36:12","modified_gmt":"2017-07-24T16:36:12","slug":"a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/","title":{"rendered":"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup"},"content":{"rendered":"<p style=\"text-align: justify;\">(Look in the <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/misconceptions\/\">Misconceptions<\/a> blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our <a href=\"https:\/\/www.sqlskills.com\/join-the-sqlskills-insider-community\/\" target=\"_blank\" rel=\"noopener noreferrer\">Insider list<\/a>, plus my online <a href=\"https:\/\/www.pluralsight.com\/courses\/sqlserver-myths\" target=\"_blank\" rel=\"noopener noreferrer\">Myths and Misconceptions training course<\/a> on Pluralsight.)<\/p>\n<p style=\"text-align: justify;\">This myth is one of the most common and I&#8217;ve come across very few people who know the truth.<\/p>\n<p><strong>Myth #20:<\/strong> <em>after breaking the log backup chain, a full database backup is required to restart it<\/em>.<\/p>\n<p><strong><u><em>FALSE<\/em><\/u><\/strong><\/p>\n<p style=\"text-align: justify;\">A normal\u00a0transaction log backup contains all the transaction log generated since the previous log backup (or since the first ever full backup if it&#8217;s the first ever log backup for the database). There are various operations that will break the log backup chain &#8211; i.e. prevent SQL Server from being able to take another log backup until the chain is restarted. The list of such operations includes:<\/p>\n<ul>\n<li>Switching from the FULL or BULK_LOGGED recovery models into the SIMPLE recovery model<\/li>\n<li>Reverting from a database snapshot<\/li>\n<li>Performing a <em>BACKUP LOG<\/em> using the <em>WITH NO_LOG<\/em> or <em>WITH TRUNCATE_ONLY<\/em> (which you can&#8217;t do any more in SQL Server 2008+)\n<ul>\n<li>See the blog post <em><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it\/\">BACKUP LOG WITH NO_LOG &#8211; use, abuse, and undocumented trace flags to stop it<\/a><\/em><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Here&#8217;s an example script that shows you what I mean:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nCREATE DATABASE LogChainTest;\r\nGO\r\nALTER DATABASE LogChainTest SET RECOVERY FULL;\r\nGO\r\nBACKUP DATABASE LogChainTest TO DISK = 'C:\\SQLskills\\LogChainTest.bck' WITH INIT;\r\nGO\r\nBACKUP LOG LogChainTest TO DISK = 'C:\\SQLskills\\LogChainTest_log1.bck' WITH INIT;\r\nGO\r\nALTER DATABASE LogChainTest SET RECOVERY SIMPLE;\r\nGO\r\nALTER DATABASE LogChainTest SET RECOVERY FULL;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nProcessed 152 pages for database 'LogChainTest', file 'LogChainTest' on file 1.\r\nProcessed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.\r\nBACKUP DATABASE successfully processed 153 pages in 0.088 seconds (14.242 MB\/sec).\r\nProcessed 2 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.\r\nBACKUP LOG successfully processed 2 pages in 0.033 seconds (0.341 MB\/sec).\r\n<\/pre>\n<p style=\"text-align: justify;\">I created a database, put it into the FULL recovery model, started the log backup chain, and then momentarily bounced it into the SIMPLE recovery model and back to FULL.<\/p>\n<p>Now if I try to take a log backup:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBACKUP LOG LogChainTest TO DISK = 'C:\\SQLskills\\LogChainTest_log2.bck' WITH INIT;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 4214, Level 16, State 1, Line 1\r\nBACKUP LOG cannot be performed because there is no current database backup.\r\nMsg 3013, Level 16, State 1, Line 1\r\nBACKUP LOG is terminating abnormally.\r\n<\/pre>\n<p style=\"text-align: justify;\">SQL Server knows\u00a0that I performed an operation which means the next log backup will NOT contain all the log generated since the previous log backup, so it doesn&#8217;t let \u00a0me do it.<\/p>\n<p style=\"text-align: justify;\">The myth says that a full database backup is required to restart the log backup chain. In reality, all I need is a data backup that bridges the LSN gap. A differential backup will do:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nBACKUP DATABASE LogChainTest TO DISK = 'C:\\SQLskills\\LogChainTest_Diff1.bck' WITH INIT, DIFFERENTIAL;\r\nGO\r\nBACKUP LOG LogChainTest TO DISK = 'C:\\SQLskills\\LogChainTest_log2.bck' WITH INIT;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nProcessed 40 pages for database 'LogChainTest', file 'LogChainTest' on file 1.\r\nProcessed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.\r\nBACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.083 seconds (4.040 MB\/sec).\r\nProcessed 1 pages for database 'LogChainTest', file 'LogChainTest_log' on file 1.\r\nBACKUP LOG successfully processed 1 pages in 0.010 seconds (0.768 MB\/sec).\r\n<\/pre>\n<p style=\"text-align: justify;\">This is really cool because you don&#8217;t need to take a (potentially very large) full database backup to be able to continue with regular log backups.<\/p>\n<p style=\"text-align: justify;\">If you have a backup strategy that involves file or filegroup backups as well as database backups, you can even restart the log backup chain after a single file differential backup! Take note, however, that to be able to restore that database, you&#8217;d need to have a data backup of each portion of it that bridges the LSN gap (i.e. a file or filegroup full or differential backup) but that&#8217;s more complicated than I want to go into in this post.<\/p>\n<p>Another myth bites the dust!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This myth is one of the most common and I&#8217;ve [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,61],"tags":[],"class_list":["post-710","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-misconceptions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup - 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\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This myth is one of the most common and I&#8217;ve [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-20T13:24:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-07-24T16:36:12+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=\"4 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\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/\",\"name\":\"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-20T13:24:00+00:00\",\"dateModified\":\"2017-07-24T16:36:12+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup\"}]},{\"@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":"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup - 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\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup - Paul S. Randal","og_description":"(Look in the Misconceptions blog category for the rest of the month&#8217;s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.) This myth is one of the most common and I&#8217;ve [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-20T13:24:00+00:00","article_modified_time":"2017-07-24T16:36:12+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/","name":"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-20T13:24:00+00:00","dateModified":"2017-07-24T16:36:12+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2030-restarting-a-log-backup-chain-requires-a-full-database-backup\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"A SQL Server DBA myth a day: (20\/30) restarting a log backup chain requires a full database backup"}]},{"@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\/710","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=710"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/710\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=710"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=710"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=710"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}