Choosing the Right Model for Your Needs
Now that you understand the recovery models, it should be relatively straightforward for you to choose one for a particular situation. Ask yourself these questions.

1. When Does Data Change in Your System? If changes only occur during scheduled batches, then the simple model of operation might be the easiest. You will only need to execute a full backup after each batch is run. For example, if your reporting database loads data each evening, this model will be the simplest and allow fast loading of data when using BULK COPY.

However, if it is crucial to capture each and every change to the database—and such changes occur throughout the day—the full model would be the most appropriate. This applies in most systems that run business operations—for example, order-taking systems. In this case, you need to go to the next step before choosing your model.

Do You Need Fast Loading of Data? If you need to capture changes that occur to your database throughout the day, you have a choice between using the full or the bulk-logged model. Both allow incremental backups. If you need to load data quickly using BULK COPY, then choose the bulk-logged model. Otherwise, choose the full model. However, before finalizing your decision, you have one more question to answer.

Do You Need Point-in-Time Recovery? If you need to be able to recover to a particular point in time, you must choose the full model. The bulk-logged model only allows you to recover to the end of a transaction log.

Applying a Model to the Database
Now that you've chosen a model, apply it to the database of your choice. To do so, issue an ALTER DATABASE statement:

ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]

In order to tell what model is being used by a particular database, execute this command:

SP_HELPDB [database name]

This stored procedure returns a wealth of information regarding the database. You will find the recovery model of the database under the Status column where it says, "RECOVERY=[name of model]".

Where Have All My Options Gone?
Those of you familiar with previous versions of SQL Server might be wondering what's become of sp_dboption. Read on...

For those of you who have used SQL Server in the past and were expecting to use the SP_DBOPTION command, see the sidebar on this topic.

Choosing a recovery model doesn't mean that you are stuck with your choice. On the contrary, it is easy to switch from one to another simply by issuing another ALTER DATABASE command. For example, you might choose to switch to BULK_LOGGED mode right before you perform a bulk copy in order to speed up that task and immediately after switching back to FULL mode in order to minimize the possible loss of data. Not only is the switch easy and painless, but backups can continue as scheduled.

Your Next Step
Choosing a model does not automatically generate backups for you. You still need to set up and schedule them. In a future article, I'll discuss the various types of backups—full, differential, transaction, and file backups—that SQL Server provides, and demonstrate how to choose among them.

If you need some immediate information on the subject you can look in Books Online in the chapter called "Backing Up and Restoring Databases."