SQL Server Backup Info: Recovery Models
Sql Server 2000 offers three recovery models: Full, Bulk-Logged, and Simple. The description of each model is displayed below. It is your responsibility as the DBA to put all of the components in place to make your recovery model possible. In other words, you must set up the appropriate backups to utilize your chosen model. Each of the recovery models incorporates different backup types, for more information on these backup types click here.
Full Recovery Model
The Full Recovery model makes use of database backups, differential backups, and transaction log backups. We'd recommend using this model for most production databases. This model provides the most flexibility and versatility for protecting data and recovering databases. It is also the most complex to maintain, but it is worth it for production databases.
Bulk Logged Recovery Model
This model is very similar to the Full recovery model with the exception that the following operations are minimally logged: SELECT INTO, BCP and BULK INSERT, CREATE INDEX, and text and image operations. This results in less log space being consumed during bulk operations, but eliminates the chance for point in time recovery when these operations are performed. You can still conduct database backups, differential backups and transaction log backups with this model, but the bulk operations will be minimally logged.
Simple Recovery Model
Basically, with the Simple Recovery Model, you use only complete and differential backups (no transaction log backups). Therefore, you are only as good as your last full or differential database backup.
What model is my database using by default?
Every database has a default recovery model in place. Every new database that is created on the server will take the recovery model of the MODEL database on the server. To see what your database recovery model is, right click on your database and choose properties. Midway down on the options tab the recovery model is displayed. In our example below, Full is the recovery model used for the Northwind database.
The table below shows the features of each of the recovery models. As mentioned previously, in most environments you'll probably want to be using the full recovery model for production databases.
|
|
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 |
