Recovery model 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.
Recovery Model Types
There are 3 types of recovery models:
- Full Recovery Model
- Simple Recovery Model
- Bulk Logged Recovery Model
Full Recovery Model:
The 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.
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:
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“.
Backup Types in Simple Recovery Model
You can take differential backup when using Simple Recovery Model, but you can not take Log Backup. 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.
Bulk Logged Recovery Model:
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 “How To Change Database Recovery Model“.