{"id":4702,"date":"2017-03-29T00:44:08","date_gmt":"2017-03-29T07:44:08","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4702"},"modified":"2017-03-29T00:44:08","modified_gmt":"2017-03-29T07:44:08","slug":"sqlskills-sql101-switching-recovery-models","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/","title":{"rendered":"SQLskills SQL101: Switching recovery models"},"content":{"rendered":"<p><em>As <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sqlskills-sql101-stored-procedures\/\" target=\"_blank\">Kimberly blogged about recently<\/a>, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our <strong>SQLskills SQL101<\/strong> blog posts, check out\u00a0<a href=\"https:\/\/www.sqlskills.com\/help\/SQL101\" target=\"_blank\">SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n<p>One of the things that can catch people out is the effect of switching out of the full recovery model temporarily. In this post I&#8217;ll briefly describe the three recovery models and then the problems you can have switching from full to simple, and from full to bulk-logged.<\/p>\n<p><strong>Recovery models<\/strong><\/p>\n<p>There are three recovery models:<\/p>\n<ul>\n<li>Full recovery model (the default and the most commonly used)\n<ul>\n<li>All modifications in the database a fully logged. This doesn&#8217;t mean that every modification has a separate log record, as some operations are logged with fewer log records while still logging the entire effect of the operation (for instance, <em>TRUNCATE TABLE<\/em> operations &#8211; see <a href=\"https:\/\/sqlperformance.com\/2013\/05\/sql-performance\/drop-truncate-log-myth\" target=\"_blank\">here for a deep explanation<\/a>).<\/li>\n<li>The transaction log will not clear (i.e. portions are available for reuse) until a transaction log backup is performed (see <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/misconceptions-around-the-log-and-log-backups-how-to-convince-yourself\/\" target=\"_blank\">here for a deep explanation<\/a>).<\/li>\n<li>All recovery options are available when a database is in the full recovery model (and has been since the last backup).<\/li>\n<\/ul>\n<\/li>\n<li>Bulk-logged recovery model\n<ul>\n<li>Some modifications (like an index rebuild or a bulk load, but NOT regular insert\/update\/deletes) can be minimally logged, which reduces the amount of log records generated so the transaction log does not have to grow really large during the operation. Note that this doesn&#8217;t change the size of subsequent log backups. For full instructions on how to get minimal logging for your operation, see the <em><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/dd425070%28v=sql.100%29.aspx\" target=\"_blank\">Data Loading Performance Guide<\/a><\/em> whitepaper, which lists all the various conditions that have to be met.<\/li>\n<li>The transaction log will not clear until a transaction log backup is performed\u00a0(exactly the same as\u00a0the full recovery model).<\/li>\n<li>Using bulk-logged, you trade off some recovery options (point-in-time restore and tail-of-the-log backups) for the performance gains associated with minimally logged operations.<\/li>\n<\/ul>\n<\/li>\n<li>Simple recovery model\n<ul>\n<li>Some modifications can be minimally logged\u00a0(exactly the same as\u00a0the bulk-logged recovery model).<\/li>\n<li>The log will not clear until a checkpoint occurs (usually automatically).<\/li>\n<li>Transaction log backups are not possible, so this is the most limited in terms of recovery options.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>Most people use the full recovery model, to allow log backups and permit all possible restore operations. The main thing to remember is that if your database uses the full or bulk-logged recovery model, you must perform periodic transaction log backups or the transaction log will grow forever.<\/p>\n<p>Some circumstances call for simple; if you don\u2019t need the ability to do point-in-time restore or zero-to-minimal data loss restores using log backups. An example would be a scratch database that\u2019s repopulated once per day and any changes can be lost or easily regenerated.<\/p>\n<p><strong>Switching to Simple<\/strong><\/p>\n<p>Often I hear of people who switch to the simple recovery model \u00a0to try to avoid transaction log growth during a bulk load or index rebuild, when what they really mean to do is to use the bulk-logged recovery model. There are also persistent myths out there that some regular operations *require* being in the simple recovery model &#8211; this is simply (ha ha) not true.<\/p>\n<p>Switching to the simple recovery model breaks the log backup chain, requiring a full or differential backup before any further log backups can be performed.<\/p>\n<p>Furthermore, it limits your ability to recover during a disaster because you\u2019ve now only got one full backup from which you can restore: the one you performed most recently. Think about it: your restore options become:<\/p>\n<ul>\n<li>Full backup after switch to simple, plus the latest\u00a0differential backup after that full (if you&#8217;re using differential backups) and any log backups since the switch back; or<\/li>\n<li>Most recent full backup before switch to simple, plus the latest differential after the switch back from simple, plus any log backups<\/li>\n<\/ul>\n<p>If that most-recent full backup (before or after the switch to simple) is damaged, you cannot restore \u2013 period. You can&#8217;t fall back on using\u00a0the next older\u00a0full backup, as that only allows the restore sequence up to, but not past, the switch to simple. Well, I guess you could do that, but then you lose all work since the switch to simple.<\/p>\n<p>Switching to the simple recovery model is not something you automate or do\u00a0repeatedly. About the only time when you would temporarily switch to simple is if your transaction log had run out of space and there is no way to allow it to clear (i.e. you cannot perform a log backup or add another log file) except by switching to simple and forcing a checkpoint operation. In that case you&#8217;re taking a drastic step to allow operations to continue, and being fully cognizant of the limited restore options available to you right then.<\/p>\n<p>Unless you have this emergency situation, or you decide to use the simple recovery model permanently, you should not switch to simple ever.<\/p>\n<p><strong>Switching to Bulk-logged<\/strong><\/p>\n<p>Switching to bulk-logged during a load or index maintenance process is acceptable to avoid transaction log growth. In fact, switching back-and-forth between full and bulk-logged repeatedly doesn\u2019t affect the log backup chain in any way. And doing so also doesn&#8217;t have any effect on log shipping or replication, but you can&#8217;t switch out of full when using database mirroring or an availability group as they mandate the full recovery model.<\/p>\n<p>However, using bulk-logged can cause problems for disaster recovery, so even though its behavior may be desirable, you may need to avoid using\u00a0it so you don&#8217;t risk compromising your disaster recovery options.<\/p>\n<p>Problem 1: a log backup that contains a minimally-logged operation cannot be used during a point-in-time restore. This means the time you specify in the <em>WITH STOPAT<\/em> clause of the restore statement cannot be a time covered by such a log backup. You can use that log backup as part of a restore sequence, and stop at any point in time after it (as long as that point in time is not covered by another log backup containing a minimally-logged operation, of course), but just not during it.<\/p>\n<p>Problem 2: if you need to perform a tail-of-the-log backup to capture all the log generated since the most recent scheduled log backup, the data files are inaccessible or damaged, and the log to be backed up contains a minimally-logged operation, that backup will fail prior to SQL Server 2008 R2, and from SQL Server 2008 R2 onward it will succeed, but be will corrupt the database when restored.<\/p>\n<p>So if you\u2019re going to use bulk-logged to save on log space during large operations, you need to make sure that a) there\u2019s no possibility you\u2019re going to want to restore between the last log backup and the next one, and b) there are no changes made to the database that you cannot recreate in case a disaster occurs and you can\u2019t take a valid tail-of-the-log backup.<\/p>\n<p>Switching recovery models between full and bulk-logged may not be as safe as you might think.<\/p>\n<p><strong>Summary<\/strong><\/p>\n<p>For every database that you\u2019re responsible for, make sure that you understand the ramifications of changing the recovery model, as doing so could cause you problems with disaster recovery.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 [&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,108,98],"tags":[],"class_list":["post-4702","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-sql101","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>SQLskills SQL101: Switching recovery models - 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\/sqlskills-sql101-switching-recovery-models\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQLskills SQL101: Switching recovery models - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-03-29T07:44:08+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=\"6 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\/sqlskills-sql101-switching-recovery-models\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/\",\"name\":\"SQLskills SQL101: Switching recovery models - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-03-29T07:44:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQLskills SQL101: Switching recovery models\"}]},{\"@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":"SQLskills SQL101: Switching recovery models - 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\/sqlskills-sql101-switching-recovery-models\/","og_locale":"en_US","og_type":"article","og_title":"SQLskills SQL101: Switching recovery models - Paul S. Randal","og_description":"As Kimberly blogged about recently, SQLskills is embarking on a new initiative to\u00a0blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about\u00a0things\u00a0that we often see done incorrectly, technologies used the wrong way,\u00a0or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/","og_site_name":"Paul S. Randal","article_published_time":"2017-03-29T07:44:08+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/","name":"SQLskills SQL101: Switching recovery models - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-03-29T07:44:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/sqlskills-sql101-switching-recovery-models\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"SQLskills SQL101: Switching recovery models"}]},{"@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\/4702","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=4702"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4702\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4702"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4702"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4702"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}