{"id":7816,"date":"2020-07-02T09:31:52","date_gmt":"2020-07-02T08:31:52","guid":{"rendered":"http:\/\/help.sdna.lu\/?p=7816&#038;lang=en"},"modified":"2020-07-02T12:16:12","modified_gmt":"2020-07-02T11:16:12","slug":"sql-server-recovery-models","status":"publish","type":"post","link":"https:\/\/sdna.lu\/?p=7816","title":{"rendered":"SQL Server recovery models"},"content":{"rendered":"\n<p class=\"has-medium-font-size wp-block-paragraph\"><strong>Overview<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery or simple model. A database can be switched to another model at any time.<br>To set the recovery model for a database, do the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Open the Microsoft SQL Server Management Studio<\/li><li>Connect to the appropriate server with the following screen:<br><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"197\" class=\"wp-image-7820\" style=\"width: 300px;\" alt=\"\" src=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-63.png\" srcset=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-63.png 477w, https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-63-300x197.png 300w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/li><li>On the databases treeview, <br><img loading=\"lazy\" decoding=\"async\" width=\"200\" height=\"62\" class=\"wp-image-7824\" style=\"width: 200px;\" alt=\"\" src=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-64.png\"><br>select your DeMaSy database and right-click on it to display the Properties screen. Select the Options tab:<br><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"133\" class=\"wp-image-7827\" style=\"width: 450px;\" alt=\"\" src=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-65.png\" srcset=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-65.png 598w, https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-65-300x89.png 300w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><br>The recovery model can be changed there.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-medium-font-size wp-block-paragraph\"><strong>Database backup\/restore under the simple recovery model<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Transaction logs are not backed up.<\/li><li>Changes since the most recent database backup are unprotected. In the event of a disaster, those changes must be redone by the user.<\/li><li>Under the simple recovery model, the database cannot be restored to a specific point in time within a specific backup.<\/li><li>If you are using a full database backup, just restore the most recent backup, as shown in the following illustration:<br><br><img loading=\"lazy\" decoding=\"async\" width=\"450\" height=\"214\" class=\"wp-image-7831\" style=\"width: 450px;\" alt=\"\" src=\"https:\/\/sdna.lu\/wp-content\/uploads\/bnrr-rmsimple1-fulldbbu.png\" srcset=\"https:\/\/sdna.lu\/wp-content\/uploads\/bnrr-rmsimple1-fulldbbu.png 499w, https:\/\/sdna.lu\/wp-content\/uploads\/bnrr-rmsimple1-fulldbbu-300x142.png 300w\" sizes=\"auto, (max-width: 450px) 100vw, 450px\" \/><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-medium-font-size wp-block-paragraph\"><strong>Database backup\/restore under the full recovery model<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>This model requires transaction log backups.<\/li><li>No work is lost due to a lost or damaged data file.<\/li><li>Can recover to a specific point in time assuming that your backups are complete up to that point in time.<\/li><li>The following illustration shows the least complex backup strategy that is possible under the full recovery model:<br><br><img loading=\"lazy\" decoding=\"async\" width=\"350\" height=\"275\" class=\"wp-image-7836\" style=\"width: 350px;\" alt=\"\" src=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-66.png\" srcset=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-66.png 378w, https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-66-300x236.png 300w\" sizes=\"auto, (max-width: 350px) 100vw, 350px\" \/><br><\/li><li>Under the full recovery model, after you restore your data backup, you must restore all subsequent transaction log backups and then recover the database.<br>Typically, recovering a database to the point of a failure involves the following basic steps:<ul><li>Backup the active transaction log (tail-log backup).<\/li><li>Restore the most recent full database backup without recovering the database (RESTORE DATABASE <em>database_name<\/em> FROM <em>backup_device<\/em> WITH NORECOVERY).<\/li><li>Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence (RESTORE LOG <em>database_name<\/em> from <em>log_backup<\/em> WITH NORECOVERY).<\/li><li>Recover the database (RESTORE DATABASE <em>database_name<\/em> WITH RECOVERY).<\/li><\/ul><\/li><li>The following illustration shows this restore sequence. After a failure occurs (1), a tail-log backup is created (2). Next the database is restored to the point of failure. This involves restoring a database backup, a subsequent differential backup if needed, and every log backup taken after the differential backup (or the full backup if not differential backup is needed), including the tail-log backup.<br><img loading=\"lazy\" decoding=\"async\" width=\"500\" height=\"338\" class=\"wp-image-7843\" style=\"width: 500px;\" src=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-67.png\" alt=\"\" srcset=\"https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-67.png 540w, https:\/\/sdna.lu\/wp-content\/uploads\/Untitled-67-300x203.png 300w\" sizes=\"auto, (max-width: 500px) 100vw, 500px\" \/><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-medium-font-size wp-block-paragraph\"><strong>Bulk-logged recovery model<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>The bulk-logged recovery model is designed for intermittent use to improve the performance of bulk (mass) imports of large amounts of data.<\/li><li>This model requires the log backups.<\/li><li>Reduces log space usage by using minimal logging for most bulk operations.<\/li><li>Point-in-time recovery is not supported.<\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">For more information about those recovery models, please navigate with the following links:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/backup-restore\/complete-database-restores-simple-recovery-model?view=sql-server-ver15\">Simple recovery model<\/a><\/li><li><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/backup-restore\/complete-database-restores-full-recovery-model?view=sql-server-ver15\">Full recovery model<\/a><\/li><\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Overview SQL Server backup and restore operations occur within the context of the recovery model of the database. Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations [&hellip;]<\/p>\n","protected":false},"author":19,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-7816","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"_links":{"self":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/7816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/users\/19"}],"replies":[{"embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7816"}],"version-history":[{"count":16,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/7816\/revisions"}],"predecessor-version":[{"id":7847,"href":"https:\/\/sdna.lu\/index.php?rest_route=\/wp\/v2\/posts\/7816\/revisions\/7847"}],"wp:attachment":[{"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sdna.lu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}