Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Using SQL Server 2000 Recovery Models-2 : Page 2

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date