Recovery Models in SQL Server 2005 – Part 2

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:
1) Simple
2) Full
3) Bulk-logged

Simple
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
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
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.

More info
Click here for more information.

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , . Bookmark the permalink.

One Response to Recovery Models in SQL Server 2005 – Part 2

  1. Pingback: SQL Server 2005 Backups « Golbing

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s