Tag Archives: MS SQL Server – Recovery models

SQL Server – Recovery Models

MS SQL Server – Recovery models

Table of Contents
___________________________________________________________________________________________________

1. Types of Recovery Models

i) Simple recovery model            – Full and Differential backup
ii) Bulk-logged recovery model – Full, differential and log backup
iii) Full recovery model                 – Full, differential and log backup

2. Find recovery model
a) Using SSMS (GUI)
b) Using T-SQL

3. Change Recovery model
a) Using SSMS (GUI)
b) Using T-SQL

___________________________________________________________________________________________________


1. Types of Recovery Models

i) Simple recovery model

-- Simple recovery model every transaction will be logged into the transaction log file, but at regular intervals the transaction log file will be TRUNCATED whenever a CHECKPOINT operation occurs. 
-- Generally used in Development environment where Database Priority/Point-in-time priority is less
-- Not supported Transaction log backups
-- No Point-in-time recovery possible
-- Data loss chances are more


ii) Bulk-logged recovery model


-- In Bulk-logged recovery model every transaction will be logged into the transaction log file, but bulk insert operations are minimally logged.
-- Supports transaction log backups 
-- No automatic Truncate of Transaction log.
-- Chances of data loss if bulk insert operations fail
-- May or may not be possible to perform point-in-time recovery
-- Disk consumption will be high when normal transactions but in bulk Disk consumption will be low

iii) Full recovery model

-- In FULL recovery model every transaction will be logged into the transaction log file. 
-- This recovery model is generally used in Production databases
-- Supports transaction log backups 
-- No automatic Truncate of Transaction log.
-- Minimal/No Data Loss. 
-- Point-in-time Recovery
-- Performance Overhead and large transactions at times can take more time.
-- The downside of this model is that it can consume a lot of disk space very fast.
-- Make sure setup regular T-Log Backup, so that after T-Log backup it will truncate T-Log (space will be released)


2. Find recovery model

a) Using SSMS (GUI)

TEST (Database) -- Right Click --- Properties

b) Using T-SQL

USE master;
GO
select [name], DATABASEPROPERTYEX([name],'recovery') As RecoveryModel
from sysdatabases
where name  in ('master','model','tempdb','msdb','test')
GO


3. Change Recovery model

a) Using SSMS (GUI)


b) Using T-SQL

-- SET RECOVERY MODEL TO FULL
USE MASTER;
GO
ALTER DATABASE TEST SET RECOVERY FULL;
GO







-- SET RECOVERY MODEL TO SIMPLE
USE MASTER;
GO
ALTER DATABASE TEST SET RECOVERY SIMPLE;
GO

 

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/