SQL Server Recovery Models contain the backup and restore operations of the database. The main concept against the designing of recovery model is to know how the transactions are logged, whether the transaction logs need backup, and what kind of restore operations are available with the recovery model?
Types of Recovery Models in SQL Server
Typically, an SQL Server database uses the full recovery model, bulk recovery model and simple recovery model. The database can be switched from one recovery model to another recovery model at any given time.
Full Recovery Model
The full recovery model need log backups. Full recovery model logs every transaction and maintains it until a transaction log backup is taken. The full recovery model can recover the data to an unpredictable point in time e.g. prior to application or user error. Using this recovery model you can redress to a new recovery model that contains a set of full backup and transaction log backups.
Advantage - The concept of the full recovery model provides the complete protection against the data loss. When unwanted things occur like application error or user error, you can restore the database to the point-in-time with the help of the transaction log.
Disadvantage – Main draw back of the full recovery model is that, you have to setup a regular transaction log backup to avoid the growth of transaction log files, otherwise the log files keep growing until the next full backup.
When to Use It – SQL Server full recovery model is highly recommended for Online Transaction Processing databases. This recovery model has some major features like alwayson, database mirroring, log shipping, transaction replication etc.
Bulk Recovery Model
The bulk recovery model is same like the full recovery model. The main advantage of using this recovery model is that the transaction logs does not grow surprisingly. Bulk recovery model does not allow the automated recovery of the transaction logs. When no bulk operation is performed by the DBA then this recovery model behaves as a Full recovery model. The transaction logs may grow if the it’s backup is not created.
The bulk recovery model is equal to the full recovery model with the exception that bulk data modification operations like index creation, select into, insert select, bcp, bulk insert etc. are minimally logged. These operations reduces the performance impact but at the same time you can not perform the action to restore in point. You may hav full recovery model for the normal operations and you can switch to bulk-logged recovery model temporarily. At the completion of bulk logged operation, users can reverse to full recovery model. Taking a transaction log backup is recommended when you need to switch back from full recovery model if point-in-time recovery is important.
Advantage – Better performance for the bulk data operations using only minimal logging for transactions and not letting the transaction log to grow quickly because of bulk data operations.
Disadvantage – Using this recovery model possibility of data losses increases.
When to Use it – Its recommended to switch to SQL Server bulk recovery model before starting any bulk operation and then roll back to full recovery model after completion of the bulk operation. Using this technique you can restore the log point-in-time (as long as your last transaction log backup does not include bulk operation) and bulk operations can have logged minimally.
> Minimal logging stands for, recovery of transactions without supporting point-in-time when information is needed.
Simple Recovery Model
As its name implies that the simple recovery model is the simplest recovery models in SQL Server. It manages a little bit of information in the SQL Server transactions log file. SQL Server truncates the transaction log files and deletes the associated information to transaction which have reached the transaction checkpoints to reuse the space leaving no transaction log entries for disaster recovery purpose.
Simple recovery model in SQL Server is able to backup the most recent full database or differential backups, it does not support the transaction log backups. In the simple recovery model, the truncation process of transaction log happens after a checkpoint or it may happen when you recently change the recovery model of your database to simple recovery model.
The management of database becomes much easier in comparison to other SQL Server recovery models but this recovery model increases the chances of database corruption. When you are using the simple recovery model then you can restore your database from the full backups or latest differential backups.This means that you will loss your data when you make changes between latest full recovery or differential recovery.
Advantage – The simple recovery model provides the ease of maintenance and there is no need of transaction log backups. It regenerates transaction log space from check-pointed transaction to be sure the growth of transaction log files are under control. Bulk logged recovery model performs much better than simple logged recovery model because it uses minimal log space and minimal logging is required.
Disadvantage – You will lose any data modification made while wanting to restore or when you want to make changes between latest full backup or differential backup at time of database failure.
When to Use it – In a data warehousing, having bulk operations, while data loading and in case of database failure, the data can be regenerated from data source. You can also prefer the simple recovery model in your development or test environment to ensure the transaction log growth is under the control.
Need of Recovery Models in SQL Server
Every SQL Server database has a property called ‘Recovery Model’, which may be either simple recovery, bulk recovery and full recovery model. Depending on your need you can use more than one recovery models in SQL Server.
Suppose, you have a (OLTP) OnLine Transaction Processing database and you want to restore the database to any point in time, it is recommended that you should use the full recovery model. If you are performing bulk operations like index creation, select into, insert select, bcp, bulk insert, etc. In this situation you need to switch from full recovery model to bulk recovery model for minimal logging time and after completion of process you can switch back to the full recovery model.
Changing Recovery Model
Using T-SQL –
ALTERDATABASE command with SET RECOVERY option used to change the recovery models in SQL Server.
For example:
ALTER DATABASE(database_name)SET RECOVERY FULL ;
You can query the sys.databases catalog view to verify the recovery model of the database as shown below:
SELECT name, recovery_model, recovery_model_desc FROM sys.databases WHERE name = 'database_name' ;
You can use the command below to change the recovery model to Bulk-logged or Simple, just replace your database name in place of (database_name)
-Changing the recovery model to bulk-logged
ALTER DATABASE (database_name) SET RECOVERY BULK ;
-Changing the recovery model to simple-logged
ALTER DATABASE (database_name) SET RECOVERY SIMPLE ;
When you create a new database, it holds the recovery model from the model database, that is by default full recovery model. You can use the ALTER DATABASE statement to change the recovery model of database, as mentioned above.
Using SQL Server Management Studio (SSMS) –
You can also change the recovery model of the database using SQL Server Management Studio. Follow the given simple steps to change the recovery model -
- Open Object Explorer
- Right click on your database under the Databases node
- Click on Properties
- In database Properties dialog box > click Options tab and change the recovery model as shown below:
Conclusion:
In this article we discussed about the types of recovery models in SQL Server and how to change the recovery model of the database using Transact-SQL and SQL Server Management Studio. This article also describes the advantages and disadvantages of SQL Server recovery models.
EmoticonEmoticon