A few weeks ago one of my blog readers asked in a comment if it is a good idea to set the Recovery Model of the model database to SIMPLE, and if there are any downsides of that approach.
I can’t emphasise it enough, but using the Recovery Model SIMPLE for a OLTP workload (which is typically read/write) is a really bad idea. And even setting the Recovery Model of the model database to SIMPLE is a really …… idea! (fill in the missing word). But I don’t want to cry in this blog posting about that fact, I just want to show you what the side-effects are when you run your OLTP databases in the SIMPLE Recovery Model.
Your Backup/Restore Strategy – it’s simple!
When I perform my SQL Server Health Checks, or when I’m doing on-site customer consulting engagements, I sometimes see production OLTP databases running in Recovery Model SIMPLE. It just freaks me out! You are playing with your life and your job!
When I then discuss that problem with the DBAs, they mostly say that they have set the database to SIMPLE because they had a problem with an ever increasing Transaction Log. A lot of people out there don’t know that you also have to perform regular Transaction Log Backups to keep the size of the Transaction Log manageable. That’s just one very important prerequisite of the default Recovery Model FULL: regular Transaction Log Backups!
In the Recovery Model SIMPLE you don’t need any Transaction Log Backups (they are also not possible), because SQL Server itself manages the Transaction Log for you, and just overrides it regularly. As a side-effect you don’t need Transaction Log Backups and your Transaction Log doesn’t grow.
But what’s the downside? You can’t perform Transaction Log Backups! Regular Transaction Log Backups are the most crucial part of any OLTP database – regardless of which RDBMS system you are using! When you are running your database in the Recovery Model SIMPLE you can only perform Full and Differential Database backups. That’s it!
But imagine now that your last Full or Differential Database Backup is already a few days (or even weeks!) old, and because of some problems (user errors, corrupted database, etc.) you have to restore your database. Wow! Your data loss will be huge, because everything since the last backup will simply be lost!
But when you perform regular Transaction Log Backups (when you use the Recovery Model FULL), the situation is quite different when you have to restore your database. Because after you have restored the last Full and Differential Backup you can also restore all the various Transaction Log Backups that have happened afterwards. This means that your possible data loss is probably quite small as long as you have performed regular and frequent Transaction Log Backups.
How frequent? It depends on how much data you are prepared to lose. That’s your so-called Recovery Point Objective (RPO). It’s mainly a requirement dictated by your business. I have worked with customers who have done Transaction Log Backups every hour, every 15 minutes, or every minute. And there are even SQL Server databases out there where Transaction Log Backups are just running continously: one backup after the next one. It really depends on your RPO and how much money you want to invest. A „no data-loss“ (which every business always wants initially…) is almost impossible and would be very expensive…
Your HA options – there are no options!
When you run your database in the Recovery Model SIMPLE your HA (High Availability) options are also very limited. Almost every modern HA technology in SQL Server (AlwaysOn Availability Groups, Database Mirroring, Transaction Log Shipping) depend on the Transaction Log and on regular Transaction Log Backups. If you want to use Availability Groups or Database Mirroring you have to run your database with the Recovery Model FULL. No other Recovery Model is supported and would also not be technically possible.
If you want to use the good old Transaction Log Shipping (which is still a great technology!) you have to run your database at least in the Recovery Model Bulk Logged. If you are using SIMPLE you just can’t use Transaction Log Shipping. Just think about these side-effects the next time that you want to change the Recovery Model of your database.
Summary
To be honest: if you have production OLTP database and you care about your data, you HAVE to use the Recovery Model FULL. There are no „it depends“ recommendations. And even setting the Recovery Model of the model database to SIMPLE is not really a good idea. So please think about all these very important things the next time you are thinking of setting the Recovery Model to SIMPLE…
Thanks for your time,
-Klaus
4 thoughts on “Why the Recovery Model SIMPLE is a really bad idea”
Klaus, this is a thought provoking post, so thanks for that.
I think that a blanket “always do this” or “never do that” statement is never fully applicable (see what i did there)
The choice of recovery model should be a decision based on a pre-defined business continuity plan (or something similar). This plan dictates how and when certain things happen. In this case, how and when a database should be restored and what constraints being too that process (e.g. recovery point objective and recovery time objective).
I agree that the FULL recovery model provides the most control and flexibility in covering this aspect of database management. However, this doesn’t mean it is the right choice in *all* cases.
As with many things in IT, ” it depends”. The choice should be an informed one and external expertise should be consulted if required.
See you in a few hours!
William
Hello William,
Thanks for your comment and the additional input to that topic.
See you soon,
-Klaus
Hi Klaus,
I agree with all you’ve said for production databases.
However, I often see developer and QA databases set for full recovery, which is not necessary unless a DBA is testing full recovery on a development or QA machine. Kind of obvious point, but I think worth making.
is it necessary for a reporting database or ETL systems. we have an ETL system and I am in dilemma whether or not a full recovery should be kept or not. I have weekly twice full backup and 4 hr diff backup and every hr log backup however I would like to suggest to keep the databases in simple recovery mode as my backups are taking lot of time as they are comm. vault backups.