SQL Server Recovery Models

There are three recovery models in SQL Server 2008 (and previous ones). Each of them has its purpose. Before we describe models, lets shortly describe how SQL Server deals with incoming data:

When data change is about to occur, data is first written to transaction log. Once transaction is completed, all changes are written to data file (not necessary on a hard drive at this point) and that transaction is marked as committed. At one point in time or another, data is really written to disk and transaction is then marked as “really done”.

Full

Until you perform backup, all that transaction log stuff is kept around. Only once you make a backup, transaction log is overwritten. This gives you possibility of data recovery at any time (in-between any transaction). Cost is size of transaction log (space is only reclaimed after backup) and need to backup transaction log in order to be able to recover from disaster.

Simple

Once data is really written to hard drive, transaction log is freed to be reused. This keeps transaction log quite small (compared to full recovery model). Bad thing is that recovery is possible only at backup times (not at any point in time) and there is no incremental backup. This can bite you in the ass if you have huge database.

Bulk-logged

As always, there is need for compromise. This one gives you small log of simple recovery model and incremental backups of full recovery model. However, you get semi-large transaction log and you will not be able to recovery at any point in time.

Which one?

I tend to use simple recovery model. It “steels” just a small amount of your disk space for transaction log and gives perfectly good solution for humble developer.

But, if you have big database, full recovery will ease burden on your server.

You make the choice.