SQL Server Backup Under the SIMPLE Recovery Model

SQL Server Backup Under the SIMPLE Recovery Model

I tend to forget how to do certain database administration tasks if I don’t do them often enough. No matter how *SIMPLE* they might be. :)

Recently at work I saw a production database server that was in the SIMPLE recovery model, that was scheduled for weekly FULL backups and daily DIFFERENTIAL backups. I was a little confused when I first saw this. I wondered, “I thought you cannot do FULL backups in SIMPLE recovery model?” So I checked out Books Online.

To my surprise, you CAN run a FULL/DIFF backup on a database in the SIMPLE recovery model. What you cannot do is run transaction log backups.

Look at what happens if you try to do a transaction log backup on a database in the SIMPLE recovery model (see snapshot below)

Backup Under the SIMPLE Recovery Model FAILS

Going back to the production database server I mentioned above. If that server crashes right before the daily DIFF backup then a total of 23:59 of data can be lost.

So the point between FULL and SIMPLE recovery model is:

FULL Recovery Model – Allows you to take transaction log backups. With log backups you can perform point-in-time restore, and minimize data loss.

SIMPLE Recovery Model – You can only take FULL backups and DIFFERENTIAL backups. You cannot take log backups, which means no point-in-time restore capability. IF you need point-in-time restore then switch your database to the FULL recovery model.

Moral of the story…

So what’s the moral of the story? You, as the DBA, need to know what your organization wants. Don’t ever assume. A very crucial question to ask in order to find out exactly what they want is, “In case of a crash, how much data are you okay with losing?” If the answer is, “NONE!”, then stick with the FULL recovery model and schedule frequent transaction log backups depending on how much data the organization is comfortable with losing. If the answer to the question is, “ah, a day or half a day’s worth of data.” Then, you can stick with SIMPLE recovery model and schedule FULL backups with intermittent DIFFERENTIAL backups.

Hope that helps!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.