SQL Server Recovery models and their roles

There are 3 recovery models in SQL Server :-
 
  • Simple
  • Full
  •  Bulk-Logged
Simple recovery model

 

  • Transaction log backups are not supported
  • Truncation of the transaction log is done automatically, thereby releasing space to the system
  • You can lose data, as there are no transaction log backups
  • When in recovery mode, data in the T-Log will not grow

 

Bulk-logged recovery model
 
 
  • Supports transaction log backups
  • As in Full mode, there is no automated process of transaction log truncation
  • Used primarily for bulk operations, such as bulk insert, thereby minimal
Full recovery model
  • Supports transaction log backups
  • Little chance of data loss under the normal circumstances
  • No automated process to truncate the log thus must have T-log backups
  • The transaction log backups must be made regularly to mark unused space available for overwriting
  • When using Full recovery mode, the T-log can get large in size
  • During most backup processes, the changes contained in the logfile are sent to the backup file

 

Scripts for Recovery Models:
 
select [name], DATABASEPROPERTYEX([name],’recovery’)
from sysdatabases
where name not in (‘master’,’model’,’tempdb’,’msdb’)
 
 
 
We can change the recovery mode of database through below:-
 
USE master;
GO
 
— Set recovery model to SIMPLE
 
ALTER DATABASE Demo SET RECOVERY SIMPLE;
GO
 
 — Set recovery model to FULL
 
ALTER DATABASE Demo SET RECOVERY FULL;
GO

 

Leave a Reply

Your email address will not be published. Required fields are marked *