Version

MSSQL 2000/2005

Date

31 Jan 2008

Summary

MSSQL provides three different recovery models to choose from: Simple, Full, Bulk-Logged.

Each option controls the growth of the database’s transaction log.

The larger the transaction log, the slower the response time can be to queries made against the database.

This article focuses on the more common recovery models of Simple and Full. For more information on Bulk-Logged Recovery Model go to the link below:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1237243,00.html

Technical Background

Before making the decision to set your database’s recovery model to either Simple or Full, one must review the type of backup utilized for your SQL database and transaction log.

Here’s a description of each based on the link above.

The Simple recovery model allows you to recover data only to the most recent full backup or differential backup.

Transaction log backups are not available because the contents of the transaction log are truncated each time a checkpoint is issued for the database.

The full recovery model can use full backups, differential backups and transaction log backups to provide a safety net against complete failure.

Along with being able to restore a full or differential backup, you can recover the database to the point of failure or to a specific point in time.

All operations, including bulk operations such as SELECT INTO, CREATE INDEX and bulk-loading data, are fully logged and recoverable.

Resolution

Based on the information above, the Argent recommends the Simple Recovery Model as the transaction log growth is kept to a minimum and queries to the database run faster. By default a new database is set to Full recovery model.

To change this setting, open Enterprise Manager (MSSQL 2000) or SQL Server Management Studio (MSSQL 2005), navigate to the target database, perform a right mouse click and choose ‘Properties’ from the menu.

From within with Database Properties window, select the Options tab (MSSQL 2000 – Screenshot 1) or the Options icon (MSSQL 2005 – Screenshot 2). The recovery model setting can then be change.

Screenshot 1

Screenshot 2