{"id":3193,"date":"2015-12-01T09:32:36","date_gmt":"2015-12-01T14:32:36","guid":{"rendered":"https:\/\/academy.sqlbak.com\/?p=3193"},"modified":"2023-10-17T05:25:51","modified_gmt":"2023-10-17T09:25:51","slug":"full-recovery-model","status":"publish","type":"post","link":"https:\/\/academy.sqlbak.com\/full-recovery-model\/","title":{"rendered":"Full Recovery Model"},"content":{"rendered":"<p>In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows the creation of full, differential, and transaction log backups thus making point-in-time recovery possible.<\/p>\n<p>You need to keep in mind that in the full recovery model, the transaction log file would be much larger, and you have to make regular log backups to keep it small.<!--more--><\/p>\n<p>Here are some tips on when the full recovery model is better to use:<\/p>\n<ul>\n<li>There is a big necessity to recover all data<\/li>\n<li>The database contains some filegroups and there is the need to restore of\u00a0read\/write secondary filegroups and, optionally, read-only filegroups one by one<\/li>\n<li>There is a necessity for point-in-time recovery<\/li>\n<li>There is a necessity for individual pages restored<\/li>\n<\/ul>\n<p>Let&#8217;s consider the following scenario:<\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-3048 size-large\" src=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Full-Recovery-Model-3-648x300.png\" alt=\"Full Recovery Model \" width=\"648\" height=\"300\" srcset=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Full-Recovery-Model-3.png 648w, https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Full-Recovery-Model-3-300x139.png 300w\" sizes=\"(max-width: 648px) 100vw, 648px\" \/>In the scenario above the failure occurs at 19:00, meaning that the changes made within the period from\u00a018:59 to 21:00 will be lost. The best way out in these circumstances is to restore the full backup (10:00) then the differential backup (16:00) plus transaction log backups (18:00 and 20:00). The transaction log\u00a0backup that was made at 20:00 allows to roll back to as far as 18:59.<\/p>\n<p>Here is the sequence of commands for restoring this database to 18:59:59:<\/p>\n<pre><span style=\"color: #0000ff;\">RESTORE DATABASE<\/span> <em>your_database<\/em> <span style=\"color: #0000ff;\">FROM DISK<\/span> = <span style=\"color: #ff0000;\">'full_10_00.bak'<\/span> <span style=\"color: #0000ff;\">WITH NORECOVERY<\/span>, <span style=\"color: #ff00ff;\">REPLACE<\/span>\r\n<span style=\"color: #0000ff;\">RESTORE DATABASE<\/span> <em>your_database<\/em> <span style=\"color: #0000ff;\">FROM DISK<\/span> = <span style=\"color: #ff0000;\">'diff_16_00.bak'<\/span><span style=\"color: #0000ff;\"> WITH NORECOVERY<\/span>\r\n<span style=\"color: #0000ff;\">RESTORE<\/span> <span style=\"color: #ff00ff;\">LOG<\/span> <em>your_database<\/em> <span style=\"color: #0000ff;\">FROM DISK<\/span> = <span style=\"color: #ff0000;\">'log_18_00.bak'<\/span> <span style=\"color: #0000ff;\">WITH NORECOVERY<\/span>\r\n<span style=\"color: #0000ff;\">RESTORE<\/span> <span style=\"color: #ff00ff;\">LOG<\/span> <em>your_database <\/em><span style=\"color: #0000ff;\">FROM DISK<\/span> = <span style=\"color: #ff0000;\">'log_20_00.bak'<\/span> <span style=\"color: #0000ff;\">WITH STOPAT<\/span> = <span style=\"color: #ff0000;\">'2015-11-19 18:59:59.000'<\/span>, <span style=\"color: #0000ff;\">RECOVERY<\/span><\/pre>\n<p>This is how you can set the Full recovery model<b>:<\/b><\/p>\n<pre><span style=\"color: #0000ff;\">ALTER DATABASE<\/span> <em>your_database<\/em> <span style=\"color: #0000ff;\">SET RECOVERY FULL<\/span><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In the full recovery model SQL Server does not truncate committed transactions until they have been backed up. It allows the creation of full, differential, and transaction log backups thus making point-in-time recovery possible. You need to keep in mind that in the full recovery model, the transaction log file would be much larger, and [&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>Full Recovery Model - Sql Server Backup Academy<\/title>\n<meta name=\"description\" content=\"Learn about SQL Server Full 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\/full-recovery-model\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Full Recovery Model - Sql Server Backup Academy\" \/>\n<meta property=\"og:description\" content=\"Learn about SQL Server Full recovery model\" \/>\n<meta property=\"og:url\" content=\"https:\/\/academy.sqlbak.com\/full-recovery-model\/\" \/>\n<meta property=\"og:site_name\" content=\"Sql Server Backup Academy\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-01T14:32:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-10-17T09:25:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Full-Recovery-Model-3-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=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/academy.sqlbak.com\/full-recovery-model\/\",\"url\":\"https:\/\/academy.sqlbak.com\/full-recovery-model\/\",\"name\":\"Full Recovery Model - Sql Server Backup Academy\",\"isPartOf\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#website\"},\"datePublished\":\"2015-12-01T14:32:36+00:00\",\"dateModified\":\"2023-10-17T09:25:51+00:00\",\"author\":{\"@id\":\"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8\"},\"description\":\"Learn about SQL Server Full recovery model\",\"breadcrumb\":{\"@id\":\"https:\/\/academy.sqlbak.com\/full-recovery-model\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/academy.sqlbak.com\/full-recovery-model\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/academy.sqlbak.com\/full-recovery-model\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Full 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":"Full Recovery Model - Sql Server Backup Academy","description":"Learn about SQL Server Full 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\/full-recovery-model\/","og_locale":"en_US","og_type":"article","og_title":"Full Recovery Model - Sql Server Backup Academy","og_description":"Learn about SQL Server Full recovery model","og_url":"https:\/\/academy.sqlbak.com\/full-recovery-model\/","og_site_name":"Sql Server Backup Academy","article_published_time":"2015-12-01T14:32:36+00:00","article_modified_time":"2023-10-17T09:25:51+00:00","og_image":[{"url":"https:\/\/academy.sqlbak.com\/wp-content\/uploads\/2015\/11\/Full-Recovery-Model-3-648x300.png"}],"author":"Alexandr Omelchenko","twitter_card":"summary","twitter_misc":{"Written by":"Alexandr Omelchenko","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/academy.sqlbak.com\/full-recovery-model\/","url":"https:\/\/academy.sqlbak.com\/full-recovery-model\/","name":"Full Recovery Model - Sql Server Backup Academy","isPartOf":{"@id":"https:\/\/academy.sqlbak.com\/#website"},"datePublished":"2015-12-01T14:32:36+00:00","dateModified":"2023-10-17T09:25:51+00:00","author":{"@id":"https:\/\/academy.sqlbak.com\/#\/schema\/person\/a579cfefacf074f062823446cad811a8"},"description":"Learn about SQL Server Full recovery model","breadcrumb":{"@id":"https:\/\/academy.sqlbak.com\/full-recovery-model\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/academy.sqlbak.com\/full-recovery-model\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/academy.sqlbak.com\/full-recovery-model\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Full 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\/3193"}],"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=3193"}],"version-history":[{"count":5,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3193\/revisions"}],"predecessor-version":[{"id":3870,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/posts\/3193\/revisions\/3870"}],"wp:attachment":[{"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/media?parent=3193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/categories?post=3193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/academy.sqlbak.com\/wp-json\/wp\/v2\/tags?post=3193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}