Getting to know the 3 Types of Recovery Models in SQL Server

If you are looking for the 3 types of Recovery Models in SQL Server then Sit back and relax as you have reached the correct destination. I would be guiding you through the concepts of Recovery Model in detail. First of all, lets talk about what the Recovery Model is. The Recovery models are designed to control transaction log maintenance.


The SQL Server backup and restore operations occur within the context of the recovery model of the database. A Recovery model is property of the database that maintains how the transactions are logged, whether the transaction logs are required to be backed up and the kinds of restore options available.

 Now coming to your main query, the 3 types of recovery models in SQL Server exists are:

• Simple Recovery Model
• Full Recovery Model
• Bulk Logged Recovery Model

 Typically, a database uses the full recovery model or simple recovery model. Now let’s dig deeper into each Recovery models in SQL Server to get to know more about them individually.

Simple Recovery Model

• No log backup is maintained in this type of recovery model. So, it automatically reclaims the log space to keep space requirement small so it eliminates the need to manage the transaction log space.
• Operations that require transaction log backups are not supported by the simple recovery model.
• The following features cannot be used in simple recovery mode:
• Log shipping
• AlwaysOn or Database mirroring
• Media recovery without data loss
• Point-in-time restores.
• The work loss exposure changes since the most recent backups are unprotected. In the events of disaster, those changes are redone.
• It can only recover to the end of the backup.

Full Recovery Model

• In this type of Recovery model, the log backup is maintained and no work is lost due to any lost or damages file.
• The database can be recovered to any arbitrary point in time.
• If the tail of the log has damaged, then the work loss exposure changes since the most recent log backup must be redone.
• I can recover the backup to any specific point of time, assuming that the backups are complete up to that point of time.

 

Bulk Logged Recovery Model

• This type of Recovery model also maintains the log backup.
• You can say Bulk Logged Backup is similar to Full Recovery Model in additional to that it permits high-performance bulk copy operations.
• It also Reduces log space usage by minimal logging for bulk operations.
• The work loss exposure changes when the log is damaged or the bulk logged operations occurred since the most recent log backup.
• It can recover to the end of the backup. Unlike in Full Recovery Model, the Specific point recovery is not supported in Bulk Logged Recovery Model.

Do you know the Recovery Model of your Database?

 Never thought of it? No worries. I am here to provide you the way to know your database’s Recovery model. Just run the simple query given below to get going.

Use master
GO
SELECT DATABASEPROPERTYEX('Your Database Name', 'Recovery') As [Recovery Model]
GO

How Can I change the Recovery Model of the Database?

Yes, you can change the Recovery model of your database. However, before taking this huge step, do take the transaction log backup. You can change the Recovery model either by using the TSQL Command or by using the SQL Server Management Studio.Execute the below TSQL code to change the recovery model of a database.

Use master

GO
/* Change Recovery Model to SIMPLE */
ALTER DATABASE YourDatabaseName  SET RECOVERY SIMPLE
GO

/*

IMPORTANT:-  Perform a Transaction Log Backup before Switching the Recovery Model to Bulk Logged

*/

/* Change Recovery Model to BULK_LOGGED */

ALTER DATABASE YourDatabaseName  SET RECOVERY BULK_LOGGED
GO

/* Change Recovery Model to FULL */ALTER DATABASE YourDatabaseName
SET RECOVERY FULL
GO

/*

IMPORTANT :-  Perform a Transaction Log Backup after Switching the Recovery Model to Full
*/

Database Administrator can also change the database recovery model using SQL Server Management Studio.

So what we have learned today about SQL Server!

In this article, we have discussed about the 3 Recovery models in SQL Server and the difference between Simple, Bulk Logged and Full recovery models. After that, we have found out the way to know about the Recovery model of your Database and then we discussed the ways of switching the Recovery models. If you are still not sure, which recovery model to use then my recommendation will be to use Full recovery model and issue Full, Differential and Transactional Log backups throughout the day to avoid data loss and to achieve Point in Time Recovery.

Read How to Save your dying Database in Emergency in this article: How to fix the Error: "Warning: you must recover this database prior to access." 

If you have any other query regarding Recovery models or anything regarding SQL then please feel free to ask in the comments section below. If you like this post then like us on Facebook and Follow on Twitter from the Social Buttons on top to get posted. Have a Healthy Database!