Different SQL Server Recovery Models Step by Step Tutorial with Examples

Recovery Models in SQL Server are basically designed to control the transaction log maintenance and to help you recover your data from a disaster. Based on the choice of Recovery Model, SQL Server decides which data it needs to retain within SQL Server transactional logs and for the time period.

The choice of a specific recovery model purely depends up on the criticality of the data which will be stored within the database and it will also determine what types of databases backups can be performed and what types of restores can be performed.

Starting SQL Server 2000 it supports their recovery models and they are:-

This is Part 2 of 16 Part SQL Server Database Backup Tutorial. Click here to read it from the beginning….

Different Recovery Models in SQL Server

• Simple Recovery Model
• Bulk-Logged Recovery Model
• Full Recovery Model

Every database can be in either of the above mentioned recovery model. However, on a instance of SQL Server every database can be in a different recovery model. Based on your data retention requirement one needs to wisely choose the database recovery model.

Different SQL Server Database Recovery Model are Simple, Bulk-Logged, and Full Recovery Models.

It is recommended not to change the database recovery model of TempDB system databases from SIMPLE. During SQL Server restart a TempDB database is recreated with SIMPLE recovery model.

How to Identify Recovery Model of a Database

Database Administrator can execute the below TSQL Code to identify the current recovery model of a database.

Use master
GO

SELECT DATABASEPROPERTYEX('MyTechMantra', 'Recovery') As [Recovery Model]
GO

DBA or a Developer can change the recovery model of the database either by using TSQL command or by using SQL Server Management Studio. Follow the example below to learn how to perform this change.

TSQL script to change database recovery model of AdventureWorksDW database to SIMPLE

Use master
GO

ALTER DATABASE AdventureWorksDW SET RECOVERY SIMPLE
GO

Using SQL Server Management Studio one can change the recovery model of SQL Server Database

Clicking Next Page button to continue reading the topics and click on the Previous Page button to revisit the previous topic.

Related Articles

• SQL Server Database Backup Tutorial with Examples
• Different SQL Server Recovery Models Step by Step Tutorial with Examples
• SQL Server SIMPLE Recovery Model Step by Step Tutorial with Examples
• SQL Server FULL Recovery Model Step by Step Tutorial with Examples
• SQL Server BULK-LOGGED Recovery Model Step by Step Tutorial with Examples
• Different Types of SQL Server Backups
• Permissions Required to Take Database Backup in SQL Server
• FULL Database Backups in SQL Server Step by Step Tutorial with Examples
• DIFFERENTIAL Database Backups in SQL Server Step by Step Tutorial with Examples
• TRANSACTION LOG Backups in SQL Server Step by Step Tutorial with Examples
• COPY_ONLY Backup in SQL Server Step by Step Tutorial with Examples
• MIRRORED Backup in SQL Server Step by Step Tutorial with Examples
• FILE Backup in SQL Server Step by Step Tutorial with Examples
• FILEGROUP Backup in SQL Server Step by Step Tutorial with Examples
• PARTIAL Backup in SQL Server Step by Step Tutorial with Examples
• TAIL-LOG Backup in SQL Server Step by Step Tutorial with Examples