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’)
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