Connect With

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter

Trending SQL Server Tips

Different SQL Server Recovery Models Step by Step Tutorial with Examples

Read Comments   |   Related Tutorials: SQL Server Database Backup Options > Next Topic: SIMPLE Recovery Model

Page 2 / 16

Click here to read this tutorial from the beginning...


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:-

Different Recovery Models in SQL Server

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.

Note: 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.

One can change the recovery model of the database any time. However, this will resulting in breaking the backup chain. Hence it is advisable to immediately take the full backup of user/system database after changing the 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

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

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


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

How to change the recovery model of SQL Server Database Using SQL Server Management Studio

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

Previous Page.. Next Page..

Learn More...

Last Updated On: Feb 12, 2014

Share this Article

Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips

Please leave your Valuable Comment or Let us know how this article helped you: