{"id":576,"date":"2017-07-06T10:54:17","date_gmt":"2017-07-06T10:54:17","guid":{"rendered":"https:\/\/www.kerneldatarecovery.com\/blog\/?p=576"},"modified":"2020-01-16T07:51:00","modified_gmt":"2020-01-16T07:51:00","slug":"which-recovery-model-to-choose-for-sql-server","status":"publish","type":"post","link":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/","title":{"rendered":"Which Recovery Model to choose for SQL Server?"},"content":{"rendered":"<p>In MS SQL Server, the Recovery Model decides how the transactions are maintained and backed up so that data can be recovered after disasters. Three recovery models are available from SQL Server 2000 onwards\u2013 simple, full, and bulk-logged. Each of these recovery models function differently from each other. And for choosing a model for your SQL Server, you should consider the criticality of the data, and your processing and backup needs.<br \/>\nLet us understand the usage and functionality of each model in detail:<\/p>\n<h4>Simple Recovery Model<\/h4>\n<p>As the name suggests, it is a basic recovery model which helps the user to restore full and differential backups. Though this model is easy to manage, it is reliable only when data is not critical. This model may not allow to restore database to a given point in time. So, the user may only restore it to the actual time when full or differential backup has occurred. You may lose any modification done in that duration.<\/p>\n<p>So, this Simple Recovery Model is recommended in the following conditions.<\/p>\n<ul>\n<li>If the database is for testing or development need only.<\/li>\n<li>If DBA can afford the chance of losing modifications in transactions after the backup.<\/li>\n<li><\/li>\n<li>If data is not crucial and can be recreated when needed<\/li>\n<\/ul>\n<p>Set <strong>Simple Recovery Model<\/strong> for your SQL Server in an easy way using SQL Server Management Studio with these steps:<\/p>\n<ol>\n<li>Launch <strong>SQL Server Management Studio<\/strong>.<\/li>\n<li>Go the <strong>user database<\/strong> name and right-click on it. Then select the <strong>Properties<\/strong> option.<\/li>\n<li>In the <strong>Database Properties<\/strong> window, click <strong>Options<\/strong> on the left panel and then under <strong>Recovery model<\/strong> section, select <strong>Simple<\/strong> from the drop-down menu.<\/li>\n<p><img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2017\/07\/recovery-models-1.jpg\" alt=\"select Simple from the drop-down menu\" class=\"alignnone size-full wp-image-3692\" width=\"702\" height=\"202\" srcset=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2017\/07\/recovery-models-1.jpg 702w, https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2017\/07\/recovery-models-1-300x86.jpg 300w\" sizes=\"(max-width: 702px) 100vw, 702px\" \/><br \/>\n<strong>Full Recovery Model<\/strong><\/p>\n<li>Click OK to save the settings.<\/li>\n<\/ol>\n<p><em><strong>Note:<\/strong> Similarly, DBA can switch to other Recovery Models by selecting the respective options from the drop-down menu under the Recovery model section mentioned above.<\/em><\/p>\n<h4>Full Recovery Model<\/h4>\n<p>It is a reliable model as compared to Simple Recovery Model as it helps to restore the lost data completely. This model allows point-in-time recovery of the database provided the user has all the valid database backups along with transactional log tail backup.<\/p>\n<p>In full recovery model, you also need to back up transaction log files to avoid its growth.<\/p>\n<p>Choose the <strong>Full Recovery Model<\/strong> in the following cases.<\/p>\n<ul>\n<li>If data is crucial and point-in-recovery is required.<\/li>\n<li>You are using Database Mirroring, Always On Availability Groups, etc<\/li>\n<li>If bearing data loss is not at all an option.<\/li>\n<\/ul>\n<p>You can set the full recovery model as you have set the simple recovery model.<\/p>\n<h4>Bulk-logged Recovery Model<\/h4>\n<p>This model is almost similar to full recovery model except that it uses minimal logging (only information required to recover the transaction is logged). It supports point-in-time recovery but helps in reducing the processing time. However, this model is a little risky as the data may get lost if the logs since the most recent backup are damaged. In that case, changes since the last backup must be redone.<\/p>\n<p>Select <strong>Bulk-logged Recovery Model<\/strong> for the following conditions.<\/p>\n<ul>\n<li>If you don\u2019t want to perform bulk operations while using critical data that cannot be lost<\/li>\n<li>If minimal logging is required to reduce more log file growth.<\/li>\n<\/ul>\n<h4>How to Recover SQL Data Without Backups?<\/h4>\n<p>SQL database recovery tools are of great help when no backups are available to restore the lost SQL data. Tools like <a href=\"https:\/\/www.kerneldatarecovery.com\/sql-recovery.html\">SQL Recovery<\/a> helps you recover data from corrupt MDF and NDF files. Also, the KDR tool supports all MS SQL versions.<\/p>\n<p align=\"center\"><a class=\"dl\" href=\"https:\/\/www.kerneldatarecovery.com\/dl\/dl.php?id=77\" onclick=\"dataLayer.push({'event': 'TrackEvent', 'eventCategory': 'Download', 'eventAction': 'SQLRecovery', 'eventLabel': 'BlogDLid=77'});\"><img decoding=\"async\" loading=\"lazy\" class=\"alignnone size-full wp-image-1659 noshadow\" src=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2018\/06\/dl-btn-new.png\" alt=\"Download Now\" width=\"160\" height=\"49\"><\/a><\/p>\n<h4>Conclusion<\/h4>\n<p>SQL Server supports different Recovery models to be secure data against disaster situations. Each model comes with different features for use in different situations, based on the criticality of data. In case, no valid backup is available for SQL database; a well-designed and efficient recovery tool should be considered for SQL data recovery without backup.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In MS SQL Server, the Recovery Model decides how the transactions are maintained and backed up so that data can be recovered after disasters. Three recovery models are available from SQL Server 2000 onwards\u2013 simple, full, and bulk-logged. Each of these recovery models function differently from each other. And for choosing a model for your [&hellip;]<\/p>\n","protected":false},"author":12,"featured_media":1370,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_stopmodifiedupdate":true,"_modified_date":""},"categories":[6],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.10 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Which Recovery Model to choose for SQL Server?<\/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.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Which Recovery Model to choose for SQL Server?\" \/>\n<meta property=\"og:description\" content=\"In MS SQL Server, the Recovery Model decides how the transactions are maintained and backed up so that data can be recovered after disasters. Three recovery models are available from SQL Server 2000 onwards\u2013 simple, full, and bulk-logged. Each of these recovery models function differently from each other. And for choosing a model for your [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Data Recovery Blog - KDR Tools\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-06T10:54:17+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-16T07:51:00+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2017\/07\/Which-Recovery-Model-to-choose-for-SQL-Server-3.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1142\" \/>\n\t<meta property=\"og:image:height\" content=\"500\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Aftab Alam\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Aftab Alam\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Which Recovery Model to choose for SQL Server?","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.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Which Recovery Model to choose for SQL Server?","og_description":"In MS SQL Server, the Recovery Model decides how the transactions are maintained and backed up so that data can be recovered after disasters. Three recovery models are available from SQL Server 2000 onwards\u2013 simple, full, and bulk-logged. Each of these recovery models function differently from each other. And for choosing a model for your [&hellip;]","og_url":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/","og_site_name":"Data Recovery Blog - KDR Tools","article_published_time":"2017-07-06T10:54:17+00:00","article_modified_time":"2020-01-16T07:51:00+00:00","og_image":[{"width":1142,"height":500,"url":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2017\/07\/Which-Recovery-Model-to-choose-for-SQL-Server-3.jpg","type":"image\/jpeg"}],"author":"Aftab Alam","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Aftab Alam","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/#article","isPartOf":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/"},"author":{"name":"Aftab Alam","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/person\/c9cd8fe08b5f5080047a483bcef1b681"},"headline":"Which Recovery Model to choose for SQL Server?","datePublished":"2017-07-06T10:54:17+00:00","dateModified":"2020-01-16T07:51:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/"},"wordCount":640,"commentCount":0,"publisher":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#organization"},"articleSection":["Database Recovery"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/","url":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/","name":"Which Recovery Model to choose for SQL Server?","isPartOf":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#website"},"datePublished":"2017-07-06T10:54:17+00:00","dateModified":"2020-01-16T07:51:00+00:00","breadcrumb":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/which-recovery-model-to-choose-for-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.kerneldatarecovery.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Which Recovery Model to choose for SQL Server?"}]},{"@type":"WebSite","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#website","url":"https:\/\/www.kerneldatarecovery.com\/blog\/","name":"Data Recovery Blog - KDR Tools","description":"Data Recovery Blog - KDR Tools","publisher":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.kerneldatarecovery.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#organization","name":"Data Recovery Blog - KDR Tools","url":"https:\/\/www.kerneldatarecovery.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/04\/logo.png","contentUrl":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2019\/04\/logo.png","width":161,"height":47,"caption":"Data Recovery Blog - KDR Tools"},"image":{"@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/person\/c9cd8fe08b5f5080047a483bcef1b681","name":"Aftab Alam","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.kerneldatarecovery.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2021\/11\/Aftab.jpg","contentUrl":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-content\/uploads\/2021\/11\/Aftab.jpg","caption":"Aftab Alam"},"url":"https:\/\/www.kerneldatarecovery.com\/blog\/author\/aftab-alam\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts\/576"}],"collection":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/users\/12"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/comments?post=576"}],"version-history":[{"count":16,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts\/576\/revisions"}],"predecessor-version":[{"id":8276,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/posts\/576\/revisions\/8276"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/media\/1370"}],"wp:attachment":[{"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/media?parent=576"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/categories?post=576"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kerneldatarecovery.com\/blog\/wp-json\/wp\/v2\/tags?post=576"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}