SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server Recovery Models - Specifications and Significance!

SQL Server provides an efficient way of backing up the databases by logging all the transactions in log file but it becomes equally important to decide which data has to be backed up and which not by limiting the logging process. In this section we will discuss about the SQL Server Recovery Models and its purpose.

What SQL Are Server Recovery Models?

SQL Server has a property which controls the way transactions must be logged and its growth. This property comes under the SQL recovery models. This controls the transaction log maintenance which can be different for different databases as per its importance. It is important to choose right model as per the requirements which can be also switched if required in future. To understand these Recovery Models it is important to understand first what “Transaction Log file” is.

Transaction Log: 

SQL Server has an internal mechanism which keeps a detailed record of all the transactions made. This includes all type of database modifications recorded as a string of log records that too in a sequence of their creation. In case of any database malfunction or corruption, these transaction logs can be utilized to bring back the database in consistent state.
There are three different types of recovery models;
  1. Simple
  2. Full
  3. Bulk-Logged
        Simple SQL Recovery Model:
    As name suggests, this model is the simplest one amongst all the models and lets the transaction log file maintain minimal amount of information. SQL Server itself truncates the log files removing the information about transactions which have reached checkpoints (has been written to data file) to limit the space. This space once freed can be used for other entries. Hence inn case of this recovery model you should take precaution on keeping the backup interval time less so that data loss is minimized but it can affect the production work.

     Pros: This model is extremely simple to be managed and requires and performs better as minimum transaction log file space is used.

     Cons:Only recent full database or differential backups’ data is recoverable. This Model has highest risks of data loss. It will not provide; Log Shipping, Point-in-time restores, AlwaysOn or Database mirroring.

     Usage: For test environment SQL databases or some cases where data can be regenerated or restored form the data source, this model can be utilized.

        Full SQL Recovery Model 

     Full Recovery model is built to record and maintain each and every transaction made until a full backup is taken which includes everything. This model can be used as a disaster recovery strategy as it comprises full backup with along with differential backup and transaction log backups. For controlling the increasing size of transaction log, it should be backed up to make it truncated. 

     Pros:This model provides complete protection and is foremost solution to keep data available even after corruption in databases. One can recover data to any point like right before the error occurred and hence is most effective strategy. 

     Cons: Maintenance can be really tough as you will need to setup log backup ensuring the growth is in control. Taking full backup can resolve the unnecessary growth of the transaction log file. 

     Usage: Databases which are extremely crucial and you don’t want to take any chances can be formulated with Full Recovery model.

        Bulk-Logged SQL Recovery Model

     Bulk-Logged recovery model is same as Full recovery model with only difference that this model handles the bulk data modification operations like (BCP, INSERT SELECT, Index Creation, BULK INSERT, etc.) using minimal logging. Minimal logging implies that only that much information will be logged which is required to recover transaction without exhibiting any-time recovery. Processing time will be saved using this model and it will also save log space usage. This in turn restricts you from any-time-recovery option.

     Pros: Transaction log’s growth can be controlled as it will to minimal logging for bulk data. 

     Cons:Any-time-recovery option will not be available like Full Recovery model. Maintenance can be complicated.

     Usage: You can switch to bulk-logged model when bulk operations has to be done and as it is done you can switch back to full recovery model. 

      How to Will You Change Recovery Model

      There are two methods to View or Change Recovery Model;
  •          SQL Server Management Studio
  •          Using Transact – SQL
     SQL Server Management Studio:

      Steps to View or Change:
  •   Connect to apt instance of SQL Database Engine.
  •   Click Server Name in Object Explorer and expand the Server tree.
  •    Expand the Databases and right-click the database.
  •   Click Properties which in turn opens Database properties dialog box.
  •   Now in Select a Page pane click the Options.
  •   Here you will be able to see current Recovery Model in its list box.
  •    By clicking the drop-down menu you can change the Recovery Model.
  •    Once done click OK.

      Using Transact – SQL:

      Steps to View Recovery Model:

  •   Connect to Database Engine.
  •   Now, from Standard bar click New Query.
  •   Copy & Paste the below arguments to query window and click on Execute.

     Steps to Change the Recovery Model:

  •   Connect to Database Engine.
  •   Again, from Standard bar click New Query.
  •      Copy & Paste the below arguments to query window and click on Execute.


    You can choose Recovery Model for your SQL databases as per your requirements. Different Recovery Models can be chosen as per the importance of databases. For e.g. a very important database must have Full Recovery Model at the backend so that there are less chances of data loss. But it is extremely important to backup the transaction log file prior changing the Recovery Model of certain database’s transaction log file as any mistake can ultimately lose the data.

Zora's SQL Tips

Hi! I am Zora Stalin, an IT geek and a passionate learner of technology. Besides my job as an Information Technology Analyst, I love searching and sharing new things that excite me help for others.


Leave a comment on the original post [sqlserveroverview.blogspot.com, opens in a new window]

Loading comments...