Being able to define a recovery model is one of my favorite features of SQL Server 2000, and it's one of the first new features I wrote about. When I wrote my June 2000 column, "Database Recovery Models" (InstantDoc ID 8551), the product was still in beta, but I felt strongly that even early adopters needed to know about this capability before upgrading their SQL Servers.
Because the recovery model you choose is still important, and because I've learned a few new things over the past 3 years, I decided it was time to revisit this topic. First, I want to review the differences between the models and make sure that you periodically reevaluate which model is right for you. Second, I want to point out some new things I've learned and show you some tricks I used to verify the new information.
The primary decision a SQL Server administrator needs to make regarding recovery models is whether to choose Full or Bulk-Logged. The third choice, Simple Recovery, is appropriate only for some of the system databases and for test or training databases that you never need to back up and can rebuild easily when needed.
The trade-offs between the Full and Bulk-Logged Recovery models revolve around six special SQL Server operations: SELECT INTO, bulk copy program (bcp) and some Data Transformation Services (DTS) operations that use bcp, BULK INSERT, CREATE INDEX, WRITETEXT, and UPDATETEXT. If you never use any of these bulk operations, there's no difference between the Full and Bulk-Logged models, and it doesn't matter which one your database is set to. If you do perform any of these operations, the trade-offs involve the operation speed, the transaction log size, the size of the transaction log backup, and the precision to which you can restore the database. With the Full model, the transaction log grows larger and the operation runs slower when you execute any of these bulk operations. With the Bulk-Logged model, the log stays smaller, but when you back up the log, the backup contains all the changed data (not just the logged record), so you might need a lot more disk space. Finally, with the Bulk-Logged model, if you perform any bulk operations, you can't recover to a particular time within a log backup; you must apply the entire log during a restore.
Because the database recovery options in SQL Server 2000 allow enough logging for recovery to always be possible, your SELECT INTO and bulk copy scripts will never fail. You'll always be able to perform these bulk operations in any of the models. In addition, in the Full and Bulk-Logged models, you can make log backups no matter what operations you've done, and your backup scripts won't break. This column isn't the place for a full analysis of each model, but let's briefly review the main differences between them.
Choose Wisely
The Full Recovery model guarantees the least risk of losing any work in the case of a damaged data file. If a database is in this model, all operations will be fully logged, which means that SQL Server will write every row inserted through a bcp or BULK INSERT operation in its entirety to the transaction log. The transaction log contains every index row that a CREATE INDEX operation generates, and SQL Server writes to the log the full text or image field an application inserts or updates through WRITETEXT or UPDATETEXT.
In the Bulk-Logged model, when you execute a bulk operation, SQL Server logs the fact that the operation occurred and also information about which extents in the database files the operation affected. Bulk operations can happen much faster and the log is smaller than in the Full Recovery model. However, when you perform one of the bulk operations in this model, SQL Server uses a global bitmap with one bit corresponding to each extent in a file to keep track of which extents the operation affected. Each bit that has a value of 1 denotes an extent that a bulk operation affected since the last time the log was backed up. During log backups, SQL Server examines the bitmap and writes all the affected extents to the backup device along with the log records. The backup is much larger than in the Full model and usually takes longer. In addition, when you're restoring transactions from log backups made in the Bulk-Logged model after a bulk operation runs, you can't have any point-in-time recovery; you must restore the entire log backup.
The Simple Recovery model allows for fast bulk operations and the simplest backup and restore strategy. SQL Server can reuse sections of the log as soon as all the transactions they contain are committed or rolled back because they're no longer needed for recovery. Thus, only full database backups and differential backups are allowed in this model. You'll get an error if you try to back up the log in the Simple Recovery model.
Prev. page
[1]
2
next page 
