Understanding SQL Server database recovery models (and learn how to shrink your log files, too)

Backing up production SQL Server databases is something that must be implemented successfully, there's no way around it. As a developer, I'm often asked by customers how I would implement a backup strategy, and thus, the conversion often turns into topics like SQL Server's recovery models.

But first, what is a SQL Server recovery model? Shortly put, the model is a per-database setting that controls how backups and restored can be implemented for that particular database. Especially, the selected model affects SQL Server's log backups, and thus how data can be restored to a particular point in time.

In SQL Server's recent versions, there are three recovery models: Simple, Full and Bulk Load. The default is Full, and this means that you can back up your databases with the CREATE BACKUP statement, but then, you must manually manage the log file and its growth.

In this Full model, the data in the log file is re-used once a full backup is made, but on disk, the log file is allowed to grow without limits. This means that on a busy database, the log file (.LDF file) can be many times larger than the actual database data (the .MDF file). Thus, it is important to make sure the log file doesn't grow to fill all available disk space.

To return an over-sized log file back to normal size, use the Shrink File command in SQL Server Management Studio: right-click your database, and choose the Tasks/Shrink/Files command from the menu. Then, in the dialog box that opens, select "Log" as the "File type". Below the lists, you can see how large the log file currently is and how much is free space. On a log file that you haven't shrinked before, the available free space is commonly over 95%.

Now, back to the recovery models. If you don't want to manually administer your log files, you can change the model to Simple, and worry less. To change the model, open the properties of the database in question in SQL Server Management Studio (SSMS), and you will see the Database Properties dialog box. Here, go to the Options page on the left, and then you can see the Recovery model setting on the right, second from the top.

The Simple model automatically keeps the size of the log file at bay, but while doing so, you will lose one important possibility: to recover database data to any point in time. In the Simple model, you can only restore to the particular point in time when you took the backup of the database data.

Contrast this to the default Full model: if you would take backups of both the database data and the log files, then you could restore your database to any point in time, up to the time when you took the backups.

But there's a gotcha: if you were only taking backups of the database data, then the default Full model won't help you at all. Thus, the assumption with the default model is that you will backup both the database data and the log data.

If you are interested in learning more about recovery models, there's a good article in TechNet Magazine about this topic in the February 2009 issue.

Keywords: SQL Server recovery model, how to make your log file smaller, why does my log file not shrink automatically, why is my .ldf file so large.