Friday, January 24, 2014

What are recovery models in SQL Server 2012 and how do you choose?

Backup and recovery are some of the most important operations in order to plan for emergencies and keep the data happy at all times.The role of recovery models in SQL Server is for controlling the transaction log maintenance. SQL Server's backup and restore operations takes place depending on what recovery model is chosen.

There are 3 types of recovery models:

  • Simple - No need for log backups. If you are concerned with transactions this is not the type to choose as the changes since most recent backup are  unprotected.
  • Full - Requires log backups. Changes to a specific point in time can be recovered.
  • Bulk logged-Requires log backups. Point in time recovery is not supported but recovery to the end of any backup is. Good for high performance bulk operations.
How do you change the model?

This is with reference to SQL Server 2012 Express. The default model for recovery operations is simple as shown.

Right click on the database node of the database to be recovered (herein database Manoa). On the Select a page that gets displayed, click Options. The related page is displayed as shown.
Here you can change the recovery model. Herein Simple was changed to Full.

Now right click the database node and to display the tasks

From the submneu click Back Up... and you will see it is reflected in the backup operations although you will not be able to chnage the model here.