Recovery Models in SQL Server

Here we are going to discuss about database recovery model in SQL Server.

Recovery Models:
1. Simple
2. Bulk Logged
3. Full

Simple Recovery Model:
a)All the operations are not logged in Log file.
b)Log file size won't grow much.
c)Suitable for LOCAL and STAGE Environment.
d)Can't recover the database to certain point in time state.

Bulk Logged Recovery Model:
a)Only minimal information logged in log file during BULK operation. So, its also called as "Minimal Logging"
b)Log File won't grow much during bulk operation.
c)Suitable during bulk operation, migration.
d)Can't recover the bulk operation data.

Full Recovery Model:
a)All the operation logged in log file.
b)Log file size grow rapidly depends upon transactions.
c)Suitable for PRODUCTION environment.
c)Can recover the database to certain point in time (if we followed proper backup plan).

Here is script to switch to different recovery model.

 ALTER DATABASE testdb SET RECOVERY SIMPLE  
 GO  
 ALTER DATABASE testdb SET RECOVERY BULK_LOGGED  
 GO  
 ALTER DATABASE testdb SET RECOVERY FULL  
 GO