Explain the Recovery model in SQL Server

Question

Ashutosh Kumar Verma · 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 databaseand 
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');
and 
View the Recovery Modeland 
To view the recovery model of the current database in SQL server,
SELECT name, recovery_model_desc
FROM master.sys.databases
WHERE name = 'MyCollegeDb';
and 
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-and 
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;
and 
Types of Recovery Models in SQL Server
There are three type of recovery model provided by SQL server,
SimpleFullBulk-logged
and 
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.
and 
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.
and 
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_LOGGEDLoad the data into the databaseOnce the data load is complete, reset the recovery model to FULLSave the database
and 
Also, Read: 
How to create user-defined role in SQL Server Database?

forum

Explain the Recovery model in SQL Server

Sandra Emily

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

Liked By