Recovery Models in SQL Server Databases

 "Recovery Model" determines how long the data is to be kept in the transaction logs. It also allows what sort of backups and restores you can perform on the database.

Types of recovery models

There are three types of recovery models in SQL Server

1. Simple
2. Full 
3. Bulk-logged

How can I change the recovery model of a database?

The recovery model of a database can be changed either using the GUI (SSMS) or using a T-SQL statement.

To change using SSMS follow these steps:

1. Right-click the database and choose options

2. Select "Options" from the pages

3. From the Recovery model drop-down list choose the appropriate one.

4. Click OK

To change using T-SQL statement use the following syntax:

      
USE master;
ALTER DATABASE YourDBName SET RECOVERY SIMPLE

You can use options SIMPLE, FULL or BULK_LOGGED

You can find out the recovery model of the databases which resides on your server using the following query.

SELECT 
	[name]
	,DATABASEPROPERTYEX([name],'Recovery') AS recovery_model
FROM
	sys.databases
WHERE
	database_id > 4   

Note: database_id > 4 will ensure that system databases information is excluded.

Now we will look into closely what options it may enable during backup/restore for each type of recovery model.

Simple Recovery Model

• You cannot take backups of the transaction log explicitly
• A database backup will always contain both data and transaction log file

• Transaction log will not grow beyond what's been defined. The space in the transaction log file will be re-used (overwritten) once the transactions are written to the data file.
• In case of a disaster transaction occurred between the last full backup and the disaster timeline cannot be recovered. (E.g: Assume we have a database in which the backups are taken every hour. (8am, 9am etc.) In case a disaster happens at 10:45am, transactions between 10am and 10:45am will be lost)

Bulk-logged Recovery Model

• You can take the backup of the transaction log explicitly
• Log truncation won't happen once the transaction log backup is taken. Hence it needs to be truncated manually (Same as the "Full" recovery model)
• In the bulk-logged recovery model, certain operations are logged minimally. E.g: Bulk import operations such as BCP operations and BULK INSERT, SELECT INTO operations. More details on such operations can be found on the following link: https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver15#MinimallyLogged

Full Recovery Model

 

• Supports transaction log backup
• Chances of a data loss are minimal (subject to the backup strategy implemented)
• Log truncation must be done manually (Upon taking the transaction log backup, it will mark unused space which will be overwritten later by future transactions)
• Transaction log could grow large compared to the "Simple" recovery model

The scope of this post is to give a brief idea of the recovery models in SQL Server. In a future post, I will explain how to bring a database to a certain date and time by having a Full Recovery Model and a good backup strategy.