Database Recovery Mode in SQL Server

This article contains information about database recovery mode in SQL Server. You will find many information about FULL,SIMPLE and BULK LOGGED Recovery models and differences between these recovery models.

What is Database Recovery Mode in SQL Server?

Database recovery mode in sql server is a database property that controls how transactions are logged.

Depending on the Recovery Model, you may or may not use some high availability options, you may or may not use some backup types.

You may ask yourself “What are the recovery models available in SQL?”

Here is your answer; 🙂

Database Recovery Models in SQL Server

There are 3 types of recovery models:

1. Full Recovery Model
2. Simple Recovery Model
3. Bulk Logged Recovery Model

Full Recovery Model in SQL Server

Full Recovery Model is the model in which all transactions are logged.

If you do not tolerate data loss, you should definitely use this Recovery Model.

It must be used in production environments. You can backup Transaction Log and restore the database to the most recent time.

For example, you create a full backup on Friday night, and differential backup on Wednesday night, and Log Backup every 2 hours.

Supporse that there was a problem at 08:10 in the morning on Thursday.

Developers want you to return to the last moment you can return before 08:10.

You need to restore Full Backup first, then the last Differential Backup, then all Log Backups after the last Differential Backup until Thursday 08:00.

This way you can go back to the last moment. Actually you will lose ten minutes.

If you are using Full Recovery Model, you should definitely backup Transaction Log.

If you do not backup Transaction Log, all processes are logged in the Transaction Log file and SQL Server will not truncate Transaction Log automatically. That’s why your disk will fill up after a while.

When you backup Transaction Log, excessive growth of the Transacation Log will be prevented because the backed transactions are truncated.

The truncate of the Transaction Log may have been frightening, but this truncate operation is not a concern. On the contrary, it is a mandatory operation. This data is available in your log backup. By using these Log Backup files, you can return to any time as I mentioned above.

How often you will backup your Transation Log is related to your organization’s policy.

SQL Server Transaction Log Backup Interval

Ask the below question to yourself to decide transaction log backup interval:

“If there is a problem with the database and I need to restore the database from the backup, how many minutes/hours/days of loss is important to me?“

Simple Recovery Model in SQL Server

All transactions are logged in this recovery model, but these transactions are automatically deleted when Checkpoint is executed on SQL Server, and the transaction log file usually does not grow.

I would recommend reading the article named “What is Database Checkpoint“.

I use “usually” expression, because sometimes a single transaction can fill the transaction log file. Warn application developers in such a cases. They must not execute such a large queries in the database.

If such a situation occurs in your system, you can shrink the Transaction Log file after the transaction is finished.

You may want to read the article titled “How To Shrink SQL Server Transaction Log“.

Full,Differential and Log Backup in Simple Recovery Model

You can create differential backup in Simple Recovery Model, but you can not create Log Backup in simple recovery model. If you try it, you will receive the below error.

Msg 4208, Level 16, State 1, Line 1

The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

If we encounter a situation like the one in the example above, we can not return to 08:00 am on Thursday because the simple recovery model does not support log backup.

We can return only Wednesday night, which is the last differential backup date.

I recommend you to use simple recovery model in test and development environments. Of course if your test or development environment is important for you, you can use Full Recovery Model, and backup Transaction Log.

Simple recovery model does not support Always ON, Database Mirroring and Log Shipping.

Simple recovery model transaction log full

Yes, your transaction log file can be full even if you use simple recovery model. Normally, in the simple recovery model, every transaction is truncated as soon as it is completed. But if the transaction is too large, it can make the transaction log full before it finishes.

What is difference between simple and full recovery model?

• In full recovery model, you can perform point in time recovery, but in simple recovery model you can not.  See “Point in time Restore With Database Recovery Advisor“
• In full recovery model, you can create transaction log backups, but in simple recovery model you can not. And If you are using Full Recovery model, you must backup your transaction log. Otherwise your transaction log will be full.

SQL Server Recovery Model Simple vs Full Performance

There is no difference in terms of performance. Only for bulk operations simple recovery model will be better.

Bulk Logged Recovery Model in SQL Server

The recovery model I generally do not prefer.

Unlike the Full Recovery Model, bulk operations such as SELECT INTO, BULK INSERT, BCP, CREATE INDEX are not logged.

The transaction log file can be prevented from growing too much with this recovery model in databases where bulk transactions are too much.

I generally do not prefer to use Bulk Logged Recovery Model;

Because; Log Backup is available, but the last log backup can not be restored if there is a record related to the bulk operation.

I prefer to use Bulk Logged Recover Model only in some cases;

In my opinion, you should only use the Bulk Logged Recovery Model temporarily.

For example, you are using the Full Recovery Model and you will have a large bulk insert, and you do not have enough space on your disk.

You can change the Database Recovery Model to Bulk Logged and change it back to Full when your bulk operations have completed.

You may want to read the article titled “Change Database Recovery Model in SQL Server“.

Difference Between Full and Bulk Logged Recovery Model in SQL Server

In full recovery model, every transaction is logged, but in bulk logged recovery model some bulk operations are not logged which I mentioned above in bulk logged recovery model section.