Understanding SQL Server database recovery models

A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged. Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.

Recovery model
Recovery Models

The three types of recovery models that you can choose from are:

  1. Full
  2. Simple
  3. Bulk-Logged

FULL – Recovery Model

The full recovery model is the most complete recovery model and allows you to recover all of your data to any point in time as long as all backup files are useable and readable. With this model all operations are fully logged which means that you can recover your database to any point. In addition, if the database is set to the full recovery model you need to also issue transaction log backups otherwise your database transaction log will continue to grow forever.

STEPs to Step-up via SSMS

STEP 1: Right click on database name and select Properties
STEP 2: Go to the Options page
STEP 3: Under Recovery model select “Full”
STEP 4: Click “OK” to save

Why FULL Recovery Model?

  • Data is critical and you want to minimize data loss.
  • You need the ability to do a point-in-time recovery.
  • You are using Database Mirroring.
  • You are using Always On Availability Groups.
Types of backups you can run when the data is in the “Full” recovery model:
  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups
Step-up FULL Recovery Model via T-SQL
ALTER DATABASE DatabaseName SET RECOVERY FULL
GO

Simple – Recovery Model

The “Simple” recovery model is the most basic recovery model for SQL Server. Every transaction is still written to the transaction log, but once the transaction is complete and the data has been written to the data file the space that was used in the transaction log file is now re-usable by new transactions. Since this space is reused there is not the ability to do a point in time recovery, therefore the most recent restore point will either be the complete backup or the latest differential backup that was completed. Also, since the space in the transaction log can be reused, the transaction log will not grow forever as was mentioned in the “Full” recovery model.

STEPs to Step-up via SSMS

STEP 1: Right click on database name and select Properties
STEP 2: Go to the Options page
STEP 3: Under Recovery model select “Simple”
STEP 4: Click “OK” to save

Why Simple Recovery Model?

  • Your data is not critical and can easily be recreated.
  • The database is only used for test or development.
  • Data is static and does not change.
  • Losing any or all transactions since the last backup is not a problem.
  • Data is derived and can easily be recreated.
Types of backups you can run when the data is in the “Simple” recovery model:
  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
Step-up Simple Recovery Model via T-SQL
ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
GO

Bulk-Logged – Recovery Model

An adjunct of the full recovery model that permits high-performance bulk copy operations such as BULK INSERT, CREATE INDEX, SELECT INTO, etc… that are not fully logged in the transaction log and therefore do not take as much space in the transaction log. Can recover to the end of any backup. Point-in-time recovery is not supported. Log backups may be of a significant size because the minimally-logged operations are captured in the log backup. If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone.

STEPs to Step-up via SSMS

STEP 1: Right click on database name and select Properties
STEP 2: Go to the Options page
STEP 3: Under Recovery model select “Bulk-logged”
STEP 4: Click “OK” to save

Why Bulk-Logged Recovery Model?

  • Data is critical and you want to minimize data loss, but you do not want to log large bulk operations.
  • Bulk operations are done at different times versus normal processing.
Types of backups you can run when the data is in the “Bulk-Logged” recovery model:
  • Complete backups
  • Differential backups
  • File and/or Filegroup backups
  • Partial backups
  • Copy-Only backups
  • Transaction log backups
Step-up Bulk-Logged Recovery Model via T-SQL
ALTER DATABASE DatabaseName SET RECOVERY BULK_LOGGED
GO

Published by Abdul Samad

Having 17+ years of extensive experience in IT industry, enabled to enhance the team performance and maximize customer satisfaction by strategically managing calls and implementing process improvements. Demonstrated ability to solve problems, meets challenging goals, and expedites delivery. Skilled MSSQL administrator guide team during the crisis situation. Apply Creative thoughts process in re-designing the workflow system to eliminate duplication of effort and increase productivity.

Leave a Reply