SQL Server Recovery Model

Introduction to SQL Server Recovery Model

Recovery Model: Is a database control property that controls:

1. How transactions are logged
2. Whether the transaction log requires/ allows backing up.
3. What kinds of restore operations are available(Simple, Full, Bulk-logged recovery model)

Create a sample DB HR, in it create Table People and insert some values:

-- Create Database HR
CREATE DATABASE HR;

GO
-- swith the current databse to HR
USE HR;

-- Create Table Poeple in DB HR
CREATE TABLE People(
Id INT IDENTITY PRIMARY KEY,
FristName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
);

--Insert some values into Poeple Table
INSERT INTO People (FristName,LastName)
    Values('John', 'Doe'),
            ('Joan', 'Njeri'),
            ('Jane', 'M'),
            ('Kyle', 'G')
GO 
-- Query all items from Table People
SELECT * FROM People;

To view the recovery model of a database use:

USE master;

GO 
/** To view Recovery model for HR DB **/

SELECT name, recovery_model_desc

FROM master.sys.databases

ORDER BY name;

Output

NOTE: It is possible to change the recovery model using;

ALTER DATABASE database_name 
SET RECOVERY recovery_model;

In this case, let's try changing the recovery model from FULL to SIMPLE

GO 
-- Change Recovery model for HR Database from FULL to SIMPLE

ALTER DATABASE HR
SET RECOVERY SIMPLE;

Output

Differences in Recovery Models

1. SIMPLE Recovery Model
SQL Server deletes transaction logs from the transaction log files at every check point. Also, this model do not store transaction records therefore making it impossible to use advanced backup strategies to minimize data loss.
Thus, use this model only if the database can be reloaded from other sources e.g. database used for reporting.

2. FULL Recovery Model
Unlike Simple recovery model, in FULL Recovery Model, SQL Server keeps the transaction log files until the BACKUP LOG statement is executed, deleting the transaction logs from the transaction log files.
Meaning, if BACKUP LOG statement is not run regularly SQL Server keeps all the transaction log files until the transaction log files are full and the database becomes inaccessible.
FULL Recovery model allows you to restore the database at any point in time.

Key Point: Schedule BACKUP LOG statement to run at regular intervals in cases of FULL Recovery Model.

2. BULK_LOGGED Recovery Model
It has almost similar behaviors to those of FULL but used in bulk-logged operations such as BULK INSERT of flat files into a database allowing recording of the operations in the transaction log files. Also, it does not allow you to perform restore of the database at any point in time.

Bulk_logged recovery model scenario:

• For a periodical bulk data load that uses FULL Recovery model, SET Recovery model to BULK_LOGGED
• Load the data into the DB
• After data load completes, SET back the recovery model to FULL
• Back up the database. For more, visit Recovery Models (SQL Server)