Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

SQL Server Recovery Models-Simple, Full and Bulk Log

What are Recovery Models?

Three recovery models are offered by every version of SQL Server. These models are Simple, Bulk-Logged and full. Among these three models, commonly used models are simple and full. These models differ in backup and recovery process and affect disk utilization and database performance if that is not properly configured. SQL Server backup and restore operations occur within the recovery model context. Basically, a recovery model is used to control transaction log maintenance.

It is a database property that can control the way in which transactions are logged and also to know whether the transaction log requires backup and the kind of restore operations available.

  • What is Simple Recovery Model?
  • What is Full Recovery Model?
  • How to Choose the Best Recovery Model?
  • Recovery Goal and Requirement Identification
  • Difference between Simple, Full and Bulk-Logged Recovery Model

In this blog, we would discuss the three recovery models and the difference between simple and full recovery model, while any database can be switched to another model at any time.

SQL Server Curriculum

What is Simple Recovery Model?

In case of Simple recovery model database is just same as the last backup. In this model all the transactions like inserts, updates, deletions and other database activities are committed immediately as and when they take place. In this model simply the amount of disc space is required for storing database and it only gets large in the case when numerous transactions take place.  As there is no need to maintain the transaction history so as a result the speed of execution increases of SQL database.

Read: How to Increase the Speed of SQL Query Execution

Now the query processing gets more time for execution and the task execution also gets faster rather than managing logs. However, there is only a single risk in this process that is catastrophic data loss. Usually, the success of database depends upon its complete transaction management process.

What is Full Recovery Model?

In case of full recovery model, the database is allowed to be restored at a point of time by using the transaction log information that helps to reach at a point at any required time. Here we can take the example of a large database insertion or deletion operation, so in such scenario, the database will be restored just before the action is performed at the database.

Here the SQL Server is managed by more than one organization that that is involved in the transaction. In case of full recovery model, the transaction log remains small enough and database performance is improved a lot as enough recoverable information is now available that can help in case of any database mishap.

How to Choose the Best Recovery Model?

Simple and full recovery models are mostly used by the organizations, but sometimes they can get stuck among both and may have to decide that which model is best for them? So to test or for the development of a database simple recovery model is appropriate while for any production database requirement, full recovery model can be the suggested, as it can be also supported by bulk-logged recovery model occasionally.

For a small production database, simple recovery model can be suitable, especially when it is a read-only data warehouse model. In order to choose the best recovery model for any organization, there are two concepts that can help the organizations one is recovery goals and the other is a requirement for the database along with your capability to manage log backups.

Read: DB2 Interview Questions and Answers

SQL Server Quiz

Recovery Goal and Requirement Identification

You should consider recovery goals and requirements for the database to select the best suitable recovery model. Following listed questions can help you in answering and identifying the availability, requirement and the sensitivity to data loss:

  • Know how important is to never miss a change in your organization?
  • Will it be easy to re-create the lost data?
  • Do you have any logically consistent database replicas?

If the answer is yes to above-mentioned questions then you should choose the full recovery database model or Microsoft Distributed Transaction Coordinator.

The pattern of Data Usage

To know the exact pattern following questions must be answered by the organizations:

  • What is the frequency of database data change
  • Are some of the tables modified frequently and significantly?
  • What is the database usage pattern in critical production period?
  • Is your database subject to risky updates?

Examples of the cases when you should use Simple Recovery Model

In the following listed conditions Simple recovery model may be used:

Read: What Is The Difference Between The SQL Inner Join And Outer Joins?
  • In case if the recovery points are unnecessary you can use Simple Recovery Model. If the database is lost then all updates between previous and failure backup will be restored
  • You can take risk of losing some database
  • You do not wish to take backup and restoring od transaction log

Examples of  the cases when you should use Full Recovery Model

In the following listed conditions Full recovery model may be used:

  • You are able to recover all data
  • You need to recover to the point of failure
  • You want to restore individual page
  • You want to incur the administrative cost of transaction log backup

SQL Server training

Difference between Simple, Full and Bulk-Logged Recovery Model

Here it is clear that backup and restores operations occur within the recovery model context. The transaction log can be maintained by recovery models. This model controls the way in which the transaction logs are maintained. Many kinds of restore operations are available and the organizations must use the appropriate one. The three recovery models have their own properties and are listed below. Organizations should use the one which is most suitable for them:

Simple Recovery Model and Other Associated Models:

Simple Recovery Model
  • No log backup is available.
  • Changes can be redone as the most recent backup are unprotected
  • It can recover only to the end of a backup
  • There is no need to maintain and manage the separate transaction log
  • Log shipping, database mirroring, media recovery features are not available
Full Recovery Model
  • It requires a log backup
  • The most recent log can be redone
  • It can recover up to a specific point in time
  • Assume that backups are complete up to some extent
Bulk-Logged
  • It requires log backups
  • If the log is damaged since the time of recent backup then it must be redone
  • It can recover the end of any backup
  • It does not support point-in-time recovery

So as per the requirement and use, organizations can use appropriate recovery model.

Read: SSRS Interview Questions & Answers For Experienced

    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews