SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

Different SQL Server Recovery Models Step by Step Tutorial with Examples

Previous Page.. Begin Tutorial.. Next Page..

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

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.

Whenever SQL Server Service is restarted a TempDB database is recreated in SIMPLE 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

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 to SIMPLE Using SSMS

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

Previous Page.. Begin Tutorial.. Next Page..

Chetna Bhalla


Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals

Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.