Database Recovery Models in SQL Server 2005

Full Recovery, Bulk Logged Recovery, and Simple Recovery Models

Full Recovery Model

The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.

For heavy OLTP databases, there is overhead associated with logging all of the transactions, and the transaction log must be continually backed up to prevent it from getting too large.

Benefits:
  • Most resistant to data loss
  • Most flexible recovery options - including point in time recovery
Disadvantages:
  • Can take up a lot of disk space
  • Requires database administrator time and patience to be used properly
(back to top)

Bulk-Logged Recovery Model

The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.

The following transactions are minimally logged in a Bulk-Logged Recovery Model:

  • SELECT INTO
  • bcp and BULK INSERT
  • CREATE INDEX
  • Text and Image operations
Benefits:
  • Transaction log stays small
  • Easier from an administration standpoint (don’t have to worry about transaction logs)
Disadvantages:
  • Not for production systems
  • Point in time recovery not possible
  • Least data resistant recovery model
(back to top)

Simple Recovery Model

The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.

Benefits:
  • Transaction log stays small
  • Easier from an administration standpoint (don’t have to worry about transaction logs)
Disadvantages:
  • Not for production systems
  • Point in time recovery not possible
  • Least data resistant recovery model
(back to top)

How To See What Recovery Model SQL Server 2005 is Using

What recovery model is my SQL Server 2005 database using? It is easy enough to find out by following the steps below.

The recovery model can be determined in SQL Server 2005 by accessing the properties of the database. To do this, you can right click on the database in Object Explorer and select "properties" as shown below.

SQL Server 2005 Recovery Models

After right clicking on properties, the SQL Server 2005 Database Properties window appears. Left click on options. The recovery model will be revealed to the right. In the screenshot below, you can see that the database is using the Full Recovery Model.

SQL Server 2005 Full Recovery Model

(back to top)

SQL Server 2005 Recovery Models Comparison Chart


 

FULL

BULK LOGGED

SIMPLE

Recommended for Production Databases

X

 

 

Standard & Enterprise Editions Default

X

 

 

Point in Time Recovery Always Possible

X

 

 

Incorporates Database Backups

X

X

X

Incorporates Transaction Log Backups

X

X

 

Can Use Differential Backups

X

X

X

Allows for High Performance Bulk Copy Operations

 

X

X

Logs All Bulk Operations

X

 

 

Uses Minimal Log Space

 

 

X


(back to top)