SQL Server 2000 offers three recovery models for each database: full recovery, simple recovery and bulk-logged recovery. The recovery models determine how much data loss is acceptable in case of a failure and what types of backup and restore functions are allowed.
Most people either select full or simple for all of their databases and just stick with the same option across the board. In most cases, selecting the full recovery model is the smartest option, because it gives you the greatest flexibility and minimizes data loss in the event a restore has to take place.
Although using the full recovery model makes logical sense, there are reasons why the other two options are available. We will further define why there are three options and when you might want to use the different options to protect your databases. First, let's take a closer look at each model.
Simple
The simple recovery model allows you to recover data only to the most recent full database 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.
Full
The full recovery model uses database backups and transaction log backups to provide complete protection against 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.
Bulk-Logged
The bulk-logged recovery model provides protection against failure combined with the best performance. In order to get better performance, the following operations are minimally logged and not fully recoverable: SELECT INTO, bulk-load operations, CREATE INDEX as well as text and image operations. Under the bulk-logged recovery model, a damaged data file can result in having to redo work manually based on the operations that are not fully logged. In addition, the bulk-logged recovery model only allows the database to be recovered to the end of a transaction log backup when the log backup contains bulk changes.
So once again, based on the information above it looks like the Full Recovery model is the way to go. Given the flexibility of the full recovery model, why would you ever select any other model? The following factors will help you determine when another model could work for you:
Select Simple if:
- Your data is not critical.
- Losing all transactions since the last full or differential backup is not an issue.
- Data is derived from other data sources and is easily recreated.
- Data is static and does not change often.
- Space is limited to log transactions. (This may be a short-term reason, but not a good long-term reason.)
Select Bulk-Logged if:
- Data is critical, but logging large data loads bogs down the system.
- Most bulk operations are done off hours and do not interfere with normal transaction processing.
- You need to be able to recover to a point in time.
Select Full if:
- Data is critical and no data can be lost.
- You always need the ability to do a point-in-time recovery.
- Bulk-logged activities are intermixed with normal transaction processing.
- You are using replication and need the ability to resynchronize all databases involved in replication to a specific point in time.
Switching recovery models
For some databases, you may need to use a combination of these recovery models. Let's say you have a critical system and you cannot afford to lose any data during daily operations; but during off hours there are maintenance tasks and data loads that use way too much transaction log space to log every transaction. In a case like this, you may want to switch recovery models prior to your maintenance tasks. This can be automated using T-SQL in the job that runs your maintenance or data load tasks. After the maintenance task is completed, the recovery model can be switched back again.
Switching between full and bulk-logged models is probably the best scenario for changing recovery models and also the safest and easiest. You can switch from any recovery model to another recovery model, but prior to or after the switch, you may need to issue additional transaction log or full backups to ensure you have a complete backup set.
How to change recovery models
There are two options that can be used to switch recovery models.
- Enterprise Manager
- Right click on the database name, select Properties, select the Options tab and select recovery model from the drop-down list. Selecting OK will change the recovery model immediately.
- T-SQL
- ALTER DATABASE Northwind SET RECOVERY FULL
GO
Summary
As you can see, the three recovery models each have a different purpose. If you are still unsure which recovery model to select, I recommend using the full recovery model and issuing both full and transaction log backups throughout the day. It becomes a little more complex to do both full and transaction log restores, but in case there is a failure in the middle of the day, you will appreciate the flexibility of point-in-time recovery.
Greg Robidoux is the president and founder of Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered several presentations at regional SQL Server users' groups and national SQL Server events. Robidoux is also the SearchSQLServer.com's Backup and Recovery expert. Ask him a question here.
