Recovery Models

 Full Databases using the full recovery model have all their operations fully logged in the transaction log and must have a backup strategy that includes full backups and transaction log backups. Starting with SQL Server 2005, full backups don’t truncate the transaction log. This is done so that the sequence of transaction log backups isn’t broken and it gives you an extra recovery option if your full backup is damaged. 

Bulk-Logged This is a special recovery model because it is intended to be used only temporarily to improve the performance of certain bulk operations by minimally logging them; all other operations are fully logged just like the full recovery model. This can improve performance because only the information required to roll back the transaction is logged. Redo information is not logged, which means you also lose point-in-time-recovery. 

 

These bulk operations include the following: ➤ BULK INSERT ➤ Using the bcp executable ➤ SELECT INTO ➤ CREATE INDEX ➤ ALTER INDEX REBUILD ➤ DROP INDEX 

 

Simple When the simple recovery model is set on a database, all committed transactions are truncated from the transaction log every time a checkpoint occurs. This ensures that the size of the log is kept to a minimum and that transaction log backups are not necessary (or even possible).