How many types of Recovery Models? Difference B/w them and Advantages and Disadvantages.
We have three types of recovery models SIMPLE, FULL, BULK-LOGGED.
In SIMPLE recovery model database log file will have only active/uncommitted transactions, all inactive/committed transactions will be truncated automatically and you can’t take log backup in simple recovery model because SQL Server will automatically truncate all the committed transactions.
In FULL recovery model database log file will have both active/uncommitted and inactive/committed transactions.
You can take the T-Log backup in FULL recovery model and you can do point-in-time recovery.
If you configure the FULL recovery model for your database you should configure the regular interval T-Log backups to maintain the log file size and for recovery purpose in case of database crash.
BULK-LOGGED, log file will have both committed and uncommitted transactions and you can take T-Log backup also but you can’t do point-in-time restore.
SQL Server will log bulk logged operations minimally i.e. BULK INSERT, BCP, SELECT INTO when you perform these operations SQL Server will log minimally, minimally means it will log only the information required to rollback the transactions and avoid transaction log file growth.
This recovery model introduced in SQL Server 2000 to control the log files growth while performing bulk operations.
please go through the fallowing links: