SQL Server Database Recovery Mode

There are three types of SQL Server Recovery modes.

1. Simple
2. Full
3. Bulk-Logged

Simple

Simply recovery mode allows you to recovery your database up until the most recent copy. This means that after a succesful transaction, you cannot rollback to an older version.

Full

Full recovery mode allows you to recovery your database up until a specific point in time. Queries like SELECT INTO, CREATE INDEX, etc. are all fully recoverable.

Bulk-Logged

Bulk-Logged recovery mode allows you to recovery most of your database up until a specific point in time. It does not recover major operations like SELECT INTO or CREATE INDEX. As a result, it requires much less storage that the Full Recover Mode.

When Do I Select What

Simple if your database is not critical.
Bulk-Logged if your data is important but that bulk operations will slow down the system.
Full if your database is critical and no data can be lost. This will have a detrimental impact on disk IO.

Note: DROP and TRUNCATE functions are not recoverable/logged. DELETE FROM is a recoverable function if you use more thorough logging on the database.

To Find the Recovery Mode of Your Database: In SQL Server Management Studio, right click on your database on go to properties. Then go to options. In that section there is a field called recovery model. That is the recovery mode for the database.

Additional Interesting Articles

SQL Server Reporting Services- Custom Chart Colors
PHP Cookie And Authentication
AJAX Javascript Functions
MS-DOS Command Descriptions
Stepwise Linear Regression in t-SQL
SQL Benchmarking - Clear Cache

©2008 AndrewKimball.com