Different SQL Server Recovery Models Step by Step Tutorial with Examples
Related Tutorials: SQL Server Database Backup Options>
Next Topic: SIMPLE Recovery Model
Page 2 / 16
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.
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
ALTER DATABASE AdventureWorksDW SET RECOVERY SIMPLE
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.
- SQL Server SIMPLE Recovery Model 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
Last Updated On: Feb 12, 2014
Please leave your Valuable Comment or Let us know how this article helped you: