Saturday, May 10, 2008

SQL server 2005 - recovery model

The SQL server 2005 has 3 recovery models. These are as follows:
  1. Full - The full recovery model is used in production settings as default recovery model. In this recovery model the database as well as the transaction logs are backed up. So the database can be recovered back to any point in time.
  2. Bulk Logged - The bulk logged recovery model can be used while doing bulk insert."BULK INSERT" will insert large amounts of data from various types of files.When the bulk logged recovery model is set the bulk inserts are not saved in the transaction logs, as it can be very taxing to save in the transaction log.
  3. Simple - This does not take any transaction log back up. Suited for a development environment.
While doing bulk inserts, the recovery model has to be changed from full to bulk logged. After finishing the bulk insert, the database has to be backed up and the recovery model has to be set back to full recovery model.

No comments: