Different SQL Server Recovery Models Step by Step Tutorial with Examples

Introduction

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.

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.

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.