{"id":699,"date":"2010-04-28T02:28:00","date_gmt":"2010-04-28T02:28:00","guid":{"rendered":"\/blogs\/paul\/post\/A-SQL-Server-DBA-myth-a-day-(2830)-BULK_LOGGED-recovery-model.aspx"},"modified":"2017-06-19T11:50:49","modified_gmt":"2017-06-19T18:50:49","slug":"a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/","title":{"rendered":"A SQL Server DBA myth a day: (28\/30) BULK_LOGGED recovery model"},"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;\">The <em>BULK_LOGGED<\/em> recovery model continues to confuse people&#8230;<\/p>\n<p style=\"text-align: justify;\"><strong>Myth #28:<\/strong> <em>various myths around the BULK_LOGGED recovery model<\/em>.<\/p>\n<p style=\"text-align: justify;\"><strong>28a)<\/strong> <em>regular DML operations can be minimally-logged<\/em><\/p>\n<p style=\"text-align: justify;\">No.<\/p>\n<p style=\"text-align: justify;\">Only a small set of bulk operations can be minimally-logged when in the <em>BULK_LOGGED<\/em> (or <em>SIMPLE<\/em>) recovery model. The list is in the Books Online topic <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms191244%28v=sql.105%29.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">Operations That Can Be Minimally Logged<\/a> has the list. This is the 2008 R2 link &#8211; make sure to check the link for the version you&#8217;re running on.<\/p>\n<p style=\"text-align: justify;\"><strong>28b)<\/strong> <em>using the BULK_LOGGED recovery model does not affect disaster recovery<\/em><\/p>\n<p style=\"text-align: justify;\">No.<\/p>\n<p style=\"text-align: justify;\">Firstly, if a minimally-logged operation has been performed since the last log backup, and one or more data files were damaged and offline because of the disaster, a tail-of-the-log backup cannot be performed and so all user transactions performed since the last log backup will be lost.<\/p>\n<p style=\"text-align: justify;\">Secondly, if a log backup contains a minimally-logged operation, a point-in-time restore cannot be performed to any time covered by the log backup. The log backup can either not be restored, or be restored in its entirety (plus additional log backups if required) &#8211; i.e. you can restore to a point:<\/p>\n<ul style=\"text-align: justify;\">\n<li>Before the beginning of that log backup<\/li>\n<li>At the end of that log backup<\/li>\n<li>After the end of that log backup<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">But you can&#8217;t restore to a point during that log backup.<\/p>\n<p style=\"text-align: justify;\"><strong>28c)<\/strong> <em>using the BULK_LOGGED recovery model also reduces the size of log backups<\/em><\/p>\n<p style=\"text-align: justify;\">No.<\/p>\n<p style=\"text-align: justify;\">A log backup that includes a minimally-logged operation must backup the minimal amount of transaction log *and* all the data file extents changed by that operation &#8211; otherwise the restore of the log backup would not fully reconstitute the minimally-logged operation. This means that log backups are roughly the same size whether in the <em>FULL<\/em> or <em>BULK_LOGGED<\/em> recovery model.<\/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.) The BULK_LOGGED recovery model continues to confuse people&#8230; Myth #28: [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[61,66,98],"tags":[],"class_list":["post-699","post","type-post","status-publish","format-standard","hentry","category-misconceptions","category-performance-tuning","category-transaction-log"],"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: (28\/30) BULK_LOGGED recovery model - 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-2830-bulk_logged-recovery-model\/\" \/>\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: (28\/30) BULK_LOGGED recovery model - 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.) The BULK_LOGGED recovery model continues to confuse people&#8230; Myth #28: [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2010-04-28T02:28:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-06-19T18:50:49+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\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/\",\"name\":\"A SQL Server DBA myth a day: (28\/30) BULK_LOGGED recovery model - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2010-04-28T02:28:00+00:00\",\"dateModified\":\"2017-06-19T18:50:49+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-2830-bulk_logged-recovery-model\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/#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: (28\/30) BULK_LOGGED recovery model\"}]},{\"@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: (28\/30) BULK_LOGGED recovery model - 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-2830-bulk_logged-recovery-model\/","og_locale":"en_US","og_type":"article","og_title":"A SQL Server DBA myth a day: (28\/30) BULK_LOGGED recovery model - 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.) The BULK_LOGGED recovery model continues to confuse people&#8230; Myth #28: [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/","og_site_name":"Paul S. Randal","article_published_time":"2010-04-28T02:28:00+00:00","article_modified_time":"2017-06-19T18:50:49+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\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/","name":"A SQL Server DBA myth a day: (28\/30) BULK_LOGGED recovery model - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2010-04-28T02:28:00+00:00","dateModified":"2017-06-19T18:50:49+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-2830-bulk_logged-recovery-model\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/a-sql-server-dba-myth-a-day-2830-bulk_logged-recovery-model\/#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: (28\/30) BULK_LOGGED recovery model"}]},{"@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\/699","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=699"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/699\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}