Explain the Recovery model in SQL Server

Question

Ashutosh Patel · Answer

SQL Server recovery model

The recovery 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

USE MyCollegeDb 
GO

Create a new table People in the MyCollegeDb database

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,

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,

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

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

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-

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.

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?

forum

Explain the Recovery model in SQL Server

Can you answer this question?

1 Answers

SQL Server recovery model

Change recovery model

Types of Recovery Models in SQL Server

Simple Recovery Model

Full Recovery Model

BULK_LOGGED return pattern