Brief intro of Recovery Models in SQL 2005

What is Recovery Model?
Recovery Model is actually a transaction log way. It has three types: Simple, Bulk-Logged and Full.
Simple recovery model leads to database engine mimimal transaction log for most operations and truncating the transaction log after each checkpoint. In this mode, "backup log" and "restore log" operations are not allowed;
Bulk-logged recover model leads to mimimal transaction log for bulk operations such as BULK INSERT and SELECT INTO. You can restore the database to the end of the log backup.
Full recover model has the database engine to log all operations into the transaction log.

Query and Change Recovery Model
We can query database recovery models by running the following statement:
SELECT name, recovery_model_desc FROM sys.databases

To change the recovery model of a database, you can use SSMS (SQL Server Management Studio) or run "ALTER DATABASE SET RECOVERY {SIMPLEBULK_LOGGEDFULL}".

-------------------------------------------------------------------
This article is owned by Wacle.
If you have any questions, please contact wacle.wang@gmail.com
-------------------------------------------------------------------