SQL Server 2000 Administration in 15 Minutes a Week: Database Backups - Page 3

June 14, 2002

Database Recovery Models 

Before jumping into a discussion on recovery models we need to first take a look back to our discussion of transaction logs from last week. Remember that all modifications made to the database are recorded in the transaction log. In the event of a failure (such as a power outage or blue screen) the transaction log can be used to reapply the changes to the database. Additionally, checkpoints are used to write all pages in memory back to the hard disk, lowering the time needed to recover the database. So once a checkpoint occurs and all the data pages are written to disk, why do we need to store information about transactions? This is where the recovery model plays a role. 

Each database running on a SQL Server can have one of three recovery models: Full, Bulk_Logged, and Simple. 

In the full recovery model, every change made to the database is logged -- all UPDATE, DELETE, and INSERT statements are logged. Additionally, certain bulk operations, such as BULK INSERT, that are used to make many modifications quickly are also logged in their entirety (i.e. each individual row added by the BULK INSERT command would be logged).

The full recovery model provides the most options in the event a data file is corrupted. When a transaction is logged and the database is in full recovery mode, the transaction stays in the log until it is backed up. Once the database is backed up, the space from the old transactions are freed and can then be used to log new transactions. Because all transactions are backed up, Full database backups make it possible to restore a backup to a "point in time" by applying only the transactions up to that point. For example, we could restore a full backup and then restore all our log backups up to a certain point before data was deleted.

If full recovery mode tracks all changes made to the database and allows us to restore transactions to a point in time, why not just use it all the time? Because all operations are logged in their entirety, you could end up with some big log files. Also, commands like BULK INSERT will be slowed down because every modification must be logged.

The bulk_logged recovery model is a lot like the full recovery mode with a few benefits and tradeoffs. Like the full recovery mode, the bulk_logged recovery model also logs all UPDATE, DELETE, and INSERT statements. However, bulk_logged only records that the operation took place for certain commands. These commands include BULK INSERT, bcp, CREATE INDEX, SELECT INTO, WRITETEXT, and UPDATETEXT. The bulk_logged recovery model is also like the full recovery model in that they do not reuse (or overwrite) log space until the transactions have been backed up.

Unlike the full recovery model, if a transaction log includes bulk operations you can not restore that log to a point in time, you must restore to the end of the log. Also, a log backup of the database may be much larger because in the bulk_logged recovery model, log backups must copy all the extents that have changed.

The benefit of the bulk_logged model is that the log file(s) for the database can be smaller if you use many bulk operations. Also, bulk operations are much faster because only the fact that the operation occurred needs to be recorded, not every modification to the database.

The last type of recovery model is the simple recovery model. Unlike the full and bulk_logged recovery models, simple recovery does not backup transaction logs. In this mode transaction logs are frequently truncated (truncation is the process of removing old transactions from the log) automatically. The simple recovery model can use full and differential backups.

In SQL Server Enterprise Edition the model database is set to the full recovery mode. Because all our databases are basically a copy of the model database, the recovery model for the databases we create is also full. You can change the model database's recovery model to another type if you want all new databases you create to start with another recovery model.

To change the recovery model for a database in Enterprise Manager, right click the database, select properties, and then choose the options tab. You can also use the ALTER DATABASE statement to change the recovery model.

Switching between the simple recovery model and another recovery model is not so "simple." In order to have the change take place you will need to make a full backup of your database. You should only use the simple recovery model for development databases -- production databases should use the full or bulk_logged recovery models.

Page 4: Making a Backup