Thursday, December 13, 2012

How to decide which Recovery model is best for your SQL database?


MS SQL Recovery models:  A Recovery model is used to provide backup and restore facility. It determines:
How transactions are logged
Speed and size of transactional log backup
Restore options

There are three recovery models available to you in SQL Server 2008, 2005 & 2000. You can switch from one recovery model to another any time. The three models are:

Simple Recovery Model: As its name implies, it is the simplest type of backup. It restores your entire database in the event of failure. In this model data can be recover only to the end of most recent backup. It supports Full backup, Differential backup, File backup, Copy-only backup and Partial backup.

When to use Simple recovery model? 
1.When you can afford to lose some data in log.
2.Data does not change.
3.When you don't want to use transactional log backup.
4.When database is used for test and development environment.
5.When data can be recreated easily.

Full Recovery model: It provides complete recovery of your database. Using full recovery model you can recover all your data to any point in time and therefore there is no loss of data. It supports Transactional log backup, Full backup, Transactional log backup, Partial backup, Copy-only backup and Differential backup.

When to use Full recovery model?
1.When you cannot afford to lose data.
2.When you want point in time recovery.
3.When you want to restore individual database pages.
4.When you can bear the cost of transactional log backup.
5.Database is used in production environment.

Bulk-Logged model: It is recommended to use this model only when you are performing large scale bulk operations. This model does not support point in time recovery of database. Bulk-logged work similar to full recovery model when there are no bulk operations. It supports Full backup, Differential backup, Transactional log backup, File backup, Copy-only backup.

When to use Bulk-Logged model?
1.When you need to perform bulk operations.
2.When point in time recovery is not required.

I hope that now you can easily decide which Recovery Model to choose for your database.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...