---
title: "Explain the Recovery model in SQL Server"  
description: "Explain the Recovery model in SQL Server"  
author: "Sandra Emily"  
published: 2024-07-15  
updated: 2024-07-17  
canonical: https://www.mindstick.com/forum/160901/explain-the-recovery-model-in-sql-server  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# Explain the Recovery model in SQL Server

[Explain](https://www.mindstick.com/forum/157854/what-is-system-debugging-explain-some-system-debugging-tools-used-in-modern-computer-systems) the [Recovery model](https://www.mindstick.com/articles/337117/explain-the-recovery-model-in-sql-server-with-an-example) in SQL Server.

## Replies

### Reply by Ashutosh Patel

#### SQL Server recovery model

The [recovery](https://www.mindstick.com/blog/23170/3-tips-on-applying-recovery-principles-to-building-a-successful-business) [model](https://yourviews.mindstick.com/view/81334/america-is-reopening-after-corona-lockdown-but-on-swedish-model) is a property of a SQL database. The recovery model controls the following:

- How SQL Server logs transactions for a database.
- Whether the transaction log of a database needs to be backed up.
- What types of restore operations are available to restore a database?

SQL Server uses the model database to set the default recovery model in the newly created databases.

Let's use the current database

```plaintext
USE MyCollegeDb
GO
```

Create a new table `People` in the `MyCollegeDb` database

```plaintext
CREATE TABLE People (
 ID int IDENTITY PRIMARY KEY,
 FirstName varchar(50) NOT NULL,
 LastName varchar(50) NOT NULL
);
```

Now, Insert some values into `People` table,

```plaintext
USE MyCollegeDb
GO
INSERT INTO People (FirstName, LastName)
 VALUES ('John', 'Doe'),
 ('Jacob', 'Mark'),
 ('John', 'Smith'),
 ('Dach', 'Keon'),
 ('Maria', 'Jaen');
```

## View the Recovery Model

To view the recovery model of the current database in SQL server,

```plaintext
SELECT name, recovery_model_desc
FROM master.sys.databases
WHERE name = 'MyCollegeDb';
```

![Explain the Recovery model in SQL Server](https://www.mindstick.com/mindstickforums/db976e54-a15f-4943-a2b4-71b955ae765c/images/a41911a2-67ac-40b2-8f37-a070441c5154.png)

Also, you can see all the recovery models of all the databases in the SQL Server,

```plaintext
SELECT  name, recovery_model_desc
FROM master.sys.databases
ORDER BY name;
```

#### Change recovery model

To change the recovery model to another, you use the following `ALTER DATABASE` statement.

## Syntax-

```plaintext
ALTER DATABASE database_name
SET RECOVERY recovery_model_name;
```

Specify the recovery model name after the `SET RECOVERY` keyword. The recovery model can be one of these, `SIMPLE`, `FULL`, and `BULK_LOGGED`.

The following example changes the recovery model of the `MyCollegeDb` database from **FULL** to **SIMPLE**.

```plaintext
ALTER DATABASE MyCollegeDb
SET RECOVERY SIMPLE;
```

#### Types of Recovery Models in SQL Server

There are three type of recovery model provided by SQL server,

- Simple
- Full
- Bulk-logged

#### Simple Recovery Model

In the `SIMPLE` recovery model, SQL Server deletes transaction logs from the transaction log files at every checkpoint.

In the `SIMPLE` recovery model, transaction logs do not store transaction records, so you cannot use advanced backup strategies to minimize data loss. This results in relatively small transaction log files.

Use the `SIMPLE` recovery model for databases whose data can be reloaded from other sources.

#### Full Recovery Model

In the `FULL` recovery model allows you to restore the database at any point in time. In this recovery model, SQL Server keeps transaction logs in transaction log files until a `BACKUP LOG` statement is executed means that the `BACKUP LOG` statement deletes the transaction logs from the transaction log files.

If you do not run the `BACKUP LOG` statement regularly, SQL Server stores all transaction information in the transaction log file until the transaction log is completed and the database is inaccessible, therefore you need to run the `BACKUP LOG` statement at regular intervals to prevent the transaction log files from becoming full.

#### BULK_LOGGED return pattern

The `BULK_LOGGED` recovery model has almost the same behavior as the `FULL` recovery model except for `bulk-logged` performance. For example, the `BULK INSERT` of the flat file containing the tables is summarized in transaction log files.

The `BULK_LOGGED` recovery model will never allow you to restore the database. A useful `BULK_LOGGED` relief scenario is the following,

- Before periodic data loads, set the recovery model to `BULK_LOGGED`
- Load the data into the database
- Once the data load is complete, reset the recovery model to `FULL`
- Save the database

**Also, Read:** [How to create user-defined role in SQL Server Database?](https://www.mindstick.com/forum/160923/how-to-create-user-defined-role-in-sql-server-database)


---

Original Source: https://www.mindstick.com/forum/160901/explain-the-recovery-model-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.