n my series of articles, “Paranoid and Proud of It,” I pointed out that systems require different backup strategies, depending on the criticality of the information stored in them. SQL Server 2000 contains a feature called “recovery models” that can help you classify your backup needs and simplify your backup plan. In this 10-Minute Solution, I explain this feature and show you how to exploit it.



SQL Server 2000 provides a new feature called “recovery models.” How do you make the best use of this feature to simplify your backup strategy?



There are several steps to take when using the recovery models:

1. Analyze how often data changes in your database and how valuable that information is.
   
2. Determine whether operations such as BULK COPY or SELECT INTO need to occur.
   
3. Determine whether you need “point-in-time” recovery.
   
4. Choose the recovery model that is most appropriate.
   
5. Set the database to the model chosen using the ALTER DATABASE command.

What Are Recovery Models?
Although you and I might work on very different databases, when it comes down to backups, most people’s requirements are met by a similar set of needs. For example, many people have development databases that they don’t need to back up that often. Likewise, many of us have worked on systems where every minute of activity is important and needs to be backed up as soon as possible. For each of these situations, a different backup strategy should be applied.

In order to make backups easier to implement, Microsoft has grouped the various strategies that can be employed into three stereotypes or models: simple, full, and bulk-logged. These SQL Serverspecific models not only help you think about your needs, but also simplify the implementation of the appropriate strategy.

Understanding Transaction Logs and Bulk Copy
Before I delve into recovery models, let me first briefly review two related features of SQL Server: the transaction log and the bulk copy utility. How the recovery models treat these two features will be one of the main differences between them.

Every database must have at least one file that is used as the transaction log. When a change is made to any information in a database, the change is first written to the transaction log before the transaction is committed. The change is only written to the database itself when a “checkpoint” occurs. During a checkpoint, all committed transactions are written to the database itself. (SQL Server periodically initiates a checkpoint for each database.).

SQL Server uses the transaction log, among other things, to ensure that committed transactions are not lost due to a power failure. A transaction log also allows transactions to be aborted and rolled back before they are committed.

Think about a transaction that moves money from your checking accounting to your savings account. The transaction requires two separate actions?a debit to your checking account and a credit to your savings account. If any change were written directly to the database, a failure between the debit and the credit would result in an imbalance in your accounts. Instead, the change is first written to the transaction log. Only after both operations have been successfully recorded in the transaction log and the transaction is committed are the changes written to the database.

SQL Server comes with a utility (the bulk copy program) that is very useful when loading large amounts of data from a file into the database. One of the ways that this utility can be faster than a similar number of INSERT statements is by allowing the bulk copy program to operate in non-logged mode. In this mode, each inserted row will not be written to the transaction log, thereby speeding up the operation. (Similarly, updates to text fields can be done in either logged or non-logged mode.)

Now let’s look at the various recovery models and see where these features come into play.

Exploring the Recovery Models
SQL Server 2000 has three models: simple, full, and bulk-logged. Let’s look at what each model means, starting with the easiest.

• Simple: The simple model describes just that: the simplest and easiest situation to manage. When a database is set to this model, there is no way you can back up only the changes made since the last backup. Instead, only full backups are allowed. One benefit of this model is that the transaction log won’t become full from transactions occurring between the full backups. Whenever the database performs a checkpoint, space in the log is reclaimed. Additionally, non-logged operations such as bulk copy are permitted.
  
  
• Full: The full model allows you to create not only complete backups of the database, but also incremental backups of only the changes that have occurred since the last full backup.
  
  An added benefit is that it’s possible to recover the database to a particular point in time. For example, if a user accidentally deletes all accounts in a database at 1 PM, it’s possible to restore the database up to 12:59 PM, right before the deletion of the accounts occurred.
  
  Under this mode, space in the transaction log is only reclaimed when a backup of the transaction log is made. When this occurs, all the changes stored in the transaction log are written to the backup and the space is freed up. Therefore, databases in this mode need to have enough space available for the transaction log to store all the transactions that occur between each backup. Additionally, non-logged operations are not allowed.
  
  
• Bulk-Logged: The bulk-logged model lies between the other two models. On the one hand, incremental backups of the database are possible. The transaction log is treated the same way in this model as in the full model. However, bulk copy operations are only minimally logged. Instead of logging each insert into the table, SQL Server only logs the minimum necessary to recover the data if the backup is needed. However, because of this, if a bulk copy operation occurs, point-in-time recovery (described in the previous paragraph) is not possible. Recovery can only stop at the end of a transaction log.

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.
   
   
2. 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.
   
   
3. 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.”