Home
Posts

Recovery Models

Anurag Dobade
A recovery model is a database property that controls following:
  • How transactions are logged, 
  • Type of backup that one could perform, and 
  • What kinds of restore operations are available. 
Three recovery models exist: 
  • simple,
  • full, and 
  • bulk-logged. 
Default Recovery Model:
  • Master  : Simple 
  • Model   :  Full 
  • Msdb    : Simple
  • Tempdb: Simple
Note: A database can be switched to another recovery model at any time.

Find Recovery Model for Database:
Right Click on Database >> Go to Properties >> Go to Option. On the Right side you can find recovery model.


Find Recovery Model for All Databases:

We can simply run the below code:

SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases 

Changing Recovery Model:

USE [master]
GO
ALTER DATABASE [clover] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO

USE [master]
GO
ALTER DATABASE [clover] SET RECOVERY SIMPLE WITH NO_WAIT
GO

USE [master]
GO
ALTER DATABASE [clover] SET RECOVERY FULL WITH NO_WAIT
GO


Simple Recovery Model

SQL Server truncates the transaction log files and deletes the associated information to transaction which have reached the transaction checkpoints to reclaim the space.

Possible backups:
  • full database backup
  • Differential backups

The following features cannot be used in simple recovery mode:
  • Log shipping
  • Always On or Database mirroring
  • Media recovery without data loss
  • Point-in-time restores

The following features used in simple recovery mode:
  • Transaction replication: Because transactions are replicated before check point occurs in transactional replication.

Full Recovery Model

Full recovery model is highly recommended for Online Transaction Processing databases because it logs every transaction and maintains it until a transaction log backup is taken. The full recovery model can recover the data to an unpredictable point in time.

Possible backups:
  • full database backup
  • Differential backups
  • Transaction log backup

The following or all features used in full recovery mode:
  • Log shipping
  • Always On or Database mirroring
  • Transactional replication 
  • Media recovery without data loss
  • Point-in-time restores

Bulk Recovery Model

The bulk recovery model is same like the full recovery model except that bulk data modification operations like index creation, select into, insert select, bcp, bulk insert etc. are minimally logged.
It provides better performance for the bulk data operations using only minimal logging for bulk operations.

Possible backups:
  • Full database backup
  • Differential backups
  • Transaction log backup

The following features used in bulk recovery mode:
  • Log shipping
  • Transaction replication: Because transactions are replicated before check point occurs in transactional replication.

The following features cannot used in bulk recovery mode:
  • Always On and Mirroring 
  • Transactional replication 
  • Media recovery without data loss
  • Point-in-time restores

Note: Switch back is possible any time without sql server restart.

At the completion of bulk logged operation, users can reverse to full recovery model or simple. Taking a transaction log backup or full backup is recommended when you need to switch back from full or simple recovery model if point-in-time recovery is important.


Post a Comment