A SQL Server database can be in one of three recovery models. FULL, BULK_LOGGED and SIMPLE. The recovery model you choose for your databases will  affect your restore plans in the event of failure or need to restore, the recovery model you pick will also influence how you mange the transaction log of your database. So you need to choose carefully.

Full

The full recovery model offers your database the most protection. Full recovery mode means all database operations are fully logged. It allows point in time and point of failure restores. It does come with some overhead though, you need to implement both Full and Log backups as part of your backup plan. You need the transaction log backups for two reasons. 1. Transaction log backups allow you to perform point of failure or point in time restores. 2. If you don’t take transaction log backups of your database when its in Full recovery mode then your transaction log will grow uncontrollably and will eventually use up all available drive space. In fact this is one of the biggest problems I have seen in my  experience, people having a database in Full recovery mode, taking regular full backups but not taking regular log backups, they only notice the problem when they run out of disk space. By backing up the log you allow the inactive portions of the log to be truncated and reused.

Bulk Logged

Bulk Logged recovery mode is not dissimilar to the Full recovery mode, Transaction log backups still need to be an integral part of your backup plan. The difference between bulk logged and full is that certain bulk operations are only minimally logged when a database is in ‘Bulk Logged’ mode. Operations such as CREATE INDEX, SELECT * INTO, BCP, BULK INSERT are only minimally logged, according to Books Online (BOL) “ Minimal logging involves logging only the information that is required to recover the transaction without supporting point-in-time recovery.” This means that if your database crashes during one of these minimally logged operations and you need to restore you will not be able to restore to a point in time.

Simple

The main advantage of the simple recovery mode is that you do not need to worry  about managing the transaction log. In fact you are not able to take transaction log backups when a database is in Simple mode. The obvious disadvantage then is that you are not able to perform a point-in-time restore when a database is in simple mode which may not be appropriate for OLTP production systems.

What do I Use?

Well in true DBA fashion, it depends. I will generalise a little here, usually for production OLTP databases I go with the full recovery model, these are business critical systems that I need to be able to recover point of failure or point in time. In this mode I will use Full backups combined with transaction log backups (differentials if needed.) on a schedule dictated by the business and what it needs in terms of recovery time. I like to copy the log backups to a different server and write the full backup to tape and store off-site. I do use the Simple recovery model on certain production systems occasionally, mainly for databases where the data remains static or does not change then I tend to go with simple with because that is usually enough and removes the need for me to manage the log.

What to consider when choosing a recover model

1. How much data loss, if any, is acceptable.

2. How often does the data change.

Setting the Recovery Model

When you create a database,  the newly created database will get it’s recovery model from the Model database.

My very simple CREATE DATABASE statement below creates a new database called ModRecTest:

CREATE DATABASE [RecModTest]
GO

The following query selects from sys.databases and selects the recovery model of both the Model database and the newly created RecModTest database:

SELECT  Name AS 'DBname', recovery_model_desc
FROM    sys.databases
WHERE   name = 'Model'
OR Name = 'RecModTest'

GO

Here are the results:

As you can see the recovery model is the same for both databases.

If you would like to change the recovery model of your database you can do this from object explorer in Management Studio by right clicking on the database in question selecting <Properties> move to the <options> tab and you can change the recovery model by choosing a different option from the drop-down list <Recovery Model> and select <OK> you can also run TSQL:

ALTER DATABASE [dbname] SET RECOVERY BULK_LOGGED

Is your Database Really in Full Recovery?

It is worth noting here, even though sys.databases tells me that my database is full recovery mode it is not in technically in ‘Full’ recovery until a Full database backup is taken. You can prove this in several ways:

1. If you try to run a transaction log backup against your database you will get an error saying that no full backup exists:

BACKUP LOG RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'

You will get an error:

2. Another way to see if your database really is in full recovery mode is to use  the following query:

SELECT d.Name, d.recovery_model_desc, dr.last_log_backup_lsn 
FROM sys.databases d 
INNER JOIN sys.database_recovery_status dr
ON d.database_id = dr.database_id
WHERE d.database_id = db_id('RecModTest')

Now if this query returns values similar to the following for your database then, even though it says it is Full recovery the lack of a value in the last_log_backup_lsn field means that a Full backup does not exist and the database is still in auto truncate (pseudo-simple) mode.

If you run a FULL database backup and run the same query again, you will see you get an LSN in the last_log_backup_lsn field. The full backup kicks off the log sequence chain and SQL Server knows to expect log backups.

BACKUP DATABASE RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'

If you create the database in simple mode, take a full backup and re-run this query you will see that the last_log_backup_lsn field is still a NULL value. This means that SQL Server does not expect log backups (It won’t allow them on a database in Simple mode) and the database in auto-truncate mode.

USE master 
GO

DROP DATABASE RecModTest 

CREATE DATABASE RecModTest
GO
ALTER DATABASE RecModTest SET RECOVERY SIMPLE

Then run a full backup:

BACKUP DATABASE RecModTest TO DISK = 'C:\DATA\Backup\RecModTest.BAK'

Then re-run the above query and we can see that the last_log_backup_lsn  field is still NULL and therefore SQL Server is not expecting log backups and is auto-truncate mode.