SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Recovery Models

In SQL Server we have three basic recovery models: full, bulk-logged, and simple. By default we find that most databases use the defaults, which mean that they are in the full recovery model.

In that case, you need to be sure that you are performing log backups, otherwise the log will grow until it reaches it’s limits, or you run out of disk space on that drive. If the log cannot record SQL Server transactions, the database cannot accept any more transactions.

The basics of recovery models are covered nicely in this article from Gail Shaw, which includes some common myths and misconceptions out there. However for the average person, the important thing is that you understand which recovery model to pick.

You Need Point in Time Recovery

Point in time recovery means recovery in between the full or differential backups. Quite a few DBAs will ask customers if they really need to recover to a point in time, and get the answer that they don’t, but that’s not often the right question to ask.

Ask your clients if the database failed at 5:00pm today, and all the work done today was lost because you restored to last night’s backup at midnight, how would they feel?

Sometimes they’re fine with the data loss, most times they aren’t. If you need to get back to a point in time between backups, make sure you use the full recovery model.

You Can Reload the Database

There are some databases, usually data warehouses, that can be rebuilt from other sources. If you take a backup of your database and then load data every day that rarely changes during the day, you might not need point in time recovery. In fact, many ETL processes are not designed for this anyway, and could not restart themselves in the middle of a load if you restored to the point in time when the database had an issue.

In this case, use the Simple recovery model.

You are space constrained with the log

If you run index rebuilds, or large data loads and find yourself with a transaction log that grows very large, you might want to investigate the Bulk-logged recovery model. This model is more confusing, so I don’t want to give you a general rule here. If you think you might benefit from less logging, investigate the bulk-logged recovery model, practice restores with it and make sure you fully understand the implications of using it before you set a database in this mode.

Filed under: Blog Tagged: administration, Backup/Recovery, disaster recovery, sql server, syndicated

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by lrutkowski on 3 October 2011

Just as an aside, I normally change my model database logging to simple, so when new databases are created they get created as simple.  Of course, I then need to remember to turn full logging on, for those databases that need it.


Posted by Steve Jones on 3 October 2011

I think the default should be simple, though I've rarely remembered to change that in new installs. That's a good tip I ought to be putting out there.

Leave a Comment

Please register or log in to leave a comment.