I posted about recovery models back in January but I wanted to do another short post that explained them a little better now that I understand them better! There are 3 recovery models used in SQL Server 2005:
Simple is the default model for SQL Server Express db’s and for some of the system databases. Typically used for small db’s or db’s where data doesn’t change frequently. Log file is automatically truncated at every checkpoint and so recovery is limited to the point of the last backup and the .ldf file uses quite a bit less storage space.
Full provides for the best data recovery of the three but does it at the expense of disk space, performance and more administration required (log backups must be scheduled and a different process for disaster recovery must be planned). Recovery can be done to any point in time and, as long as the logs are undamaged, no data is lost except for the transactions actually in process at failure.
Moving from Simple to Full or back again is allowed at any time but it is wise to do full backups after the process. The same applies to Bulk-logged.
Bulk-logged is a version of Full that is designed to decrease the size and running time of the transaction log when doing bulk operations like create index, bulk loads, select into, writetext and updatetext. Instead of storing all the details for these, it simply stores the page-level data of the outcome.
Click here for more information.