SQL Server Recovery Model
Tags: database, programming, sql
Published: 2022-10-07 12:52:51

##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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gt92btt2m5oa3b4bprva.png)
**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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2g80xphr1ol9l1rlzafq.png)

###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)](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver16)