Understanding SQL Server Recovery Model

http://www.mytechmantra.com/LearnSQLServer/SQL_Server_Recovery_Model.html

Introduction

Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. There are basically three different types of recovery models available in SQL Server 2000 and higher versions namely Simple, Full and Bulk Logged. The choice of a specific recovery model purely depends up on the criticality of the data which will be stored within the database.
Let us now take a look at each of these recovery models in detail.

Simple Recovery Model

In Simple recovery model SQL Server will automatically truncate the transactional log file during the following scenarios.

• Whenever the transaction log file is 70% full
• A CHECKPOINT command is executed internally or it is executed manually
• Whenever the active portion of the transaction log file exceeds the size that SQL Server could recover within the time specified in recovery interval (min) parameter using SP_CONFIGURE.

However, when a database is configured to use a Simple Recovery Model you will not be able to perform the transaction log backup this is by design from Microsoft.
Since you cannot take the transaction log backup, Point in Time recovery is not possible. You can restore your database only to the last available Full or Differential backups. Hence, this recovery model is best suited for user databases which are running in Development or Testing environments or a database which is configured as read-only.

Important Note: - A very common misunderstanding is that when a database is configured to use Simple recovery model nothing is logged. However this is not at all true. In Simple recovery model everything is logged but Point in Time is not possible as it’s not possible to take the transaction log backup. At the same time the Bulk operations are logged minimally as like in Bulk Logged recovery model.

Full Recovery Model

When you are using Full or Bulk Logged Recovery Model Point in Time recovery of the database is possible as long as you have all the valid database backups along with the transactional log tail backup. In Full Recovery model all the transactions are retained within the transaction log file until the log file is backed up.
In Full recovery model all the bulk operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX, ALTER INDEX, and REBUILD INDEX etc are fully logged and it can be recovered using the available backups.

By default whenever a new database is created it will be created using Full Recovery Model. This is because Model Database is configured to run under Full Recovery Model. When you are using Full Recovery Model database administrator should use a combination of Full, Differential and Transactional Log backups as part of database backup plan to avoid any data loss.

Important Note: - As a Best Practice database administrator should make sure that all the user databases in a Production environment are configured to use Full recovery model and you are using a combination of Full, Differential and Transactional Log to backups as part of database backup plan to avoid any data loss.

Bulk Logged Recovery Model

When a database is configured to use a Bulk Logged Recovery Model then SQL Server will log minimal amount of information for operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX etc within the transaction log file there by reducing the log file size. In Point in Time recovery of the database is possible only if the last transaction log doesn’t have any Bulk Logged operations. It is advisable to switch the recovery model from Full to Bulk Logged while performing Bulk Logged operations as this will help to reduce the log file growth and will thereby improve the database performance. However, as like in Full recovery model the transaction log will continue to grow until the log file is backed up.

Can I switch Database Recovery Models?

This is a very common question which comes to everyone’s mind. The simple answer to this question is YES. However, it is recommended to take the transaction log backup before switching the recovery model from Full to Bulk Logged. Once you have performed the bulk logged operations the DBA should immediately switch back to Full Recovery Model and initiate a Transitional Log backup. The below snippet explains the above scenario.

Reference: MSDN, for more information you can check the following link

How to Identify Recovery Model of a Database

Database Administrator can execute the below TSQL Code to identify the current recovery model of a database.

Use master
GO

SELECT DATABASEPROPERTYEX('AdventureWorksDW', 'Recovery') As [Recovery Model]
GO

How to change the Recovery Model of the Database

Database administrator can change the recovery model of the database 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 AdventureWorksDW
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 AdventureWorksDW
SET RECOVERY BULK_LOGGED
GO

/* Change Recovery Model to FULL */
ALTER DATABASE AdventureWorksDW
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.

Conclusion

In this article you have seen the difference between Simple, Bulk Logged and Full 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.

References

MSDN: http://msdn.microsoft.com/en-us/library/ms189275.aspx
MSDN: http://msdn.microsoft.com/en-us/library/ms190203.aspx


Read more: http://www.mytechmantra.com/LearnSQLServer/SQL_Server_Recovery_Model.html#ixzz3CKzouwJ6
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook