{"id":2650,"date":"2015-11-23T03:48:29","date_gmt":"2015-11-23T08:48:29","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=2650"},"modified":"2023-10-17T05:34:28","modified_gmt":"2023-10-17T09:34:28","slug":"bulk-logged-recovery-model","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/","title":{"rendered":"Bulk-logged Recovery Model"},"content":{"rendered":"<p>The bulk-logged recovery model is\u00a0designed for intermittent use to improve the performance of bulk imports of large amounts of data. It&#8217;s practically\u00a0the same as the full recovery model with the only exception\u00a0that under the bulk-logged recovery model some operations are logged minimally. These operations are:<\/p>\n<ul>\n<li>Bulk import operations (bcp, INSERT &#8230; SELECT, and BULK INSERT).<\/li>\n<li>SELECT INTO operations.<\/li>\n<li>Partial updates to large value data types using the WRITE clause in the UPDATE statement when inserting or appending new data. Please note that minimal logging is not used when existing values are updated.<\/li>\n<li>Inserting or appending new data into the <span class=\"input\">text<\/span>, <span class=\"input\">ntext<\/span>, and <span class=\"input\">image<\/span> data type columns\u00a0using the WRITETEXT and UPDATETEXT statements. Please note that minimal logging is not used when existing values are updated.<\/li>\n<li>If the database recovery model is set to either bulk-logged or simple, some INDEX DDL operations are minimally logged regardless of whether the operation is executed online or offline. (Minimally logged index operations are as follows:\u00a0CREATE INDEX,\u00a0ALTER INDEX REBUILD or DBCC DBREINDEX and\u00a0DROP INDEX).<\/li>\n<\/ul>\n<p><!--more--><\/p>\n<p>Though the bulk-logged recovery model reduces log space usage by using minimal logging for most bulk-logged\u00a0operations, it is recommended to temporarily switch to bulk-logged recovery model right before performing these operations and then immediately switch back to the full recovery model. This allows to minimize the use of bulk-logged recovery model.<\/p>\n<p>There is the risk of data loss for these bulk-copy operations\u00a0because bulk-logging operations prevent\u00a0recapturing changes on a transaction-by-transaction basis. The point-in-time recovery with the bulk-logged recovery model is not possible\u00a0because none of the minimally logged operations can be restored.<\/p>\n<p>Let&#8217;s consider another scenario:<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-3049 size-large\" src=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Bulk-logged-recovery-model-2-648x300.png\" alt=\"Bulk-logged recovery model \" width=\"648\" height=\"300\" srcset=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Bulk-logged-recovery-model-2.png 648w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Bulk-logged-recovery-model-2-300x139.png 300w\" sizes=\"(max-width: 648px) 100vw, 648px\" \/><\/p>\n<p>This picture shows that the failure occurs at 15:00, but before this a minimally logged operation took place and the next transaction log backup (marked yellow, at 16:00) contains bulk-logged changes. This means\u00a0that point-in-time recovery is possible only up to the previous log backup. If you try to restore your database to later point-in-time you&#8217;ll get the following errors:<\/p>\n<pre><span style=\"color: #ff0000;\">This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.<\/span>\r\n\r\n<span style=\"color: #ff0000;\">The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.<\/span>\r\n\r\n<span style=\"color: #ff0000;\">RESTORE LOG is terminating abnormally<\/span><\/pre>\n<p>So the best sequence of actions in this case is to restore the database to its state as of 14:00. All other changes will be irretrievably lost.<\/p>\n<p>Here&#8217;s how to do it:<\/p>\n<pre><span style=\"color: #0000ff;\">RESTORE DATABASE <em><span style=\"color: #000000;\">your_database<\/span><\/em> FROM DISK <span style=\"color: #000000;\">=<\/span> <span style=\"color: #ff0000;\">'full_10_00.bak'<\/span> WITH NORECOVERY<span style=\"color: #000000;\">,<\/span> <span style=\"color: #ff00ff;\">REPLACE<\/span>\r\nRESTORE <span style=\"color: #ff00ff;\">LOG<\/span> <em><span style=\"color: #000000;\">your_database<\/span> <\/em>FROM DISK <span style=\"color: #000000;\">=<\/span> <span style=\"color: #ff0000;\">'log_12_00.bak'<\/span> WITH NORECOVERY\r\nRESTORE<\/span> <span style=\"color: #ff00ff;\">LOG <\/span><span style=\"color: #000000;\"><em>your_database<\/em> <\/span><span style=\"color: #0000ff;\">FROM DISK <\/span><span style=\"color: #000000;\">=<\/span> <span style=\"color: #ff0000;\">'log_14_00.bak'<\/span><span style=\"color: #0000ff;\"> WITH <\/span><span style=\"color: #0000ff;\">RECOVERY<\/span><\/pre>\n<p>It is also important to mention that under the bulk-logged recovery model if a log backup covers any bulk operations it contains both log records and the data pages that were changed by bulk operations. \u00a0Also, if read\/write database is changed to read-only access after a bulk-logged operation, subsequent log backups might capture more data than necessary. This is because the data file cannot be updated to track which data extents were changed by a bulk-logged operation.<\/p>\n<p>Use the following command to set bulk-logged recovery model :<\/p>\n<pre><span style=\"font-weight: 400; color: #0000ff;\">ALTER DATABASE<\/span><span style=\"font-weight: 400;\">\u00a0<i>your_database<\/i> <span style=\"color: #0000ff;\">SET RECOVERY BULK_LOGGED<\/span><\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>The bulk-logged recovery model is\u00a0designed for intermittent use to improve the performance of bulk imports of large amounts of data. It&#8217;s practically\u00a0the same as the full recovery model with the only exception\u00a0that under the bulk-logged recovery model some operations are logged minimally. These operations are: Bulk import operations (bcp, INSERT &#8230; SELECT, and BULK INSERT). [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[158],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.6 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Bulk-logged Recovery Model - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Bulk-logged Recovery Model\" \/>\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\/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=\"Bulk-logged Recovery Model - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Bulk-logged Recovery Model\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-11-23T08:48:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:34:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Bulk-logged-recovery-model-2-648x300.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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/\",\"url\":\"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/\",\"name\":\"Bulk-logged Recovery Model - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-11-23T08:48:29+00:00\",\"dateModified\":\"2023-10-17T09:34:28+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Bulk-logged Recovery Model\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Bulk-logged Recovery Model\"}]},{\"@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":"Bulk-logged Recovery Model - Sql Server Backup Academy","description":"Learn about SQL Server Bulk-logged Recovery Model","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\/bulk-logged-recovery-model\/","og_locale":"en_US","og_type":"article","og_title":"Bulk-logged Recovery Model - Sql Server Backup Academy","og_description":"Learn about SQL Server Bulk-logged Recovery Model","og_url":"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-11-23T08:48:29+00:00","article_modified_time":"2023-10-17T09:34:28+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Bulk-logged-recovery-model-2-648x300.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/","url":"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/","name":"Bulk-logged Recovery Model - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-11-23T08:48:29+00:00","dateModified":"2023-10-17T09:34:28+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Bulk-logged Recovery Model","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/bulk-logged-recovery-model\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Bulk-logged Recovery Model"}]},{"@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\/2650"}],"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=2650"}],"version-history":[{"count":39,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2650\/revisions"}],"predecessor-version":[{"id":3875,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/2650\/revisions\/3875"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=2650"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=2650"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=2650"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}