Using Recovery Models with SQL Server 2005 Database Engine
(Page 1 of 5 )
In this fifth part to a six-part series on administering SQL Server 2005 database engine, you will learn how to select and use an appropriate recovery model, and more. This article is excerpted from chapter one of the book
SQL Server 2005 Management and Administration, written by Chris Amaris, Alec Minty and Ross Mistry (Sams Publishing, 2008; ISBN: 0672329565).
Understanding and Effectively Using Recovery Models
Each recovery model handles recovery differently. Specifically, each model differs in how it manages logging, which results in whether an organization’s database can be recovered to the point of failure. The three recovery models associated with a database in the Database Engine are
- Full—This recovery model captures and logs all transactions, making it possible to restore a database to a determined point-in-time or up-to-the-minute. Based on this model, you must conduct maintenance on the transaction log to prevent logs from growing too large and disks becoming full. When you perform backups, space is made available once again and can be used until the next planned backup. Organizations may notice that maintaining a transaction log slightly degrades SQL Server performance because all transactions to the database are logged. Organizations that insist on preserving critical data often overlook this issue because they realize that this model offers them the highest level of recovery capabilities.
- Simple—This model provides organizations with the least number of options for recovering data. The Simple recovery model truncates the transaction log after each backup. This means a database can be recovered only up until the last successful full or differential database backup. This recovery model also provides the least amount of administration because transaction log backups are not permitted. In addition, data entered into the database after a successful full or differential database backup is unrecoverable. Organizations that store data they do not deem as mission critical may choose to use this model.
- Bulk-Logged—This recovery model maintains a transaction log and is similar to the Full recovery model. The main difference is that transaction logging is minimal during bulk operations to maximize database performance and reduce the log size when large amounts of data are inserted into the database. Bulk import operations such as BCP, BULK INSERT, SELECT INTO, CREATE INDEX, ALTER INDEX REBUILD, and DROP INDEX are minimally logged.
Since the Bulk-Logged recovery model provides only minimal logging of bulk operations, you cannot restore the database to the point of failure if a disaster occurs during a bulk-logged operation. In most situations, an organization will have to restore the database, including the latest transaction log, and rerun the Bulk-Logged operation.
This model is typically used if organizations need to run large bulk operations that degrade system performance and do not require point-in-time recovery.
Note
When a new database is created, it inherits the recovery settings based on the Model database. The default recovery model is set to Full.
Next, you need to determine which model best suits your organization’s needs. The following section is designed to help you choose the appropriate model.
Next: Selecting the Appropriate Recovery Model >>
More Database Articles
More By Sams Publishing
