bas de zwart (9/23/2009)
I almost can't believe that *any* recovery model is presented like this as a 'best practice'; and certainly not as WHY you SHOULD be using this or that recovery model.It depends totally on the following factors:
- What kind of organisation is it?
- How many transactions are there per hour/minute/second
- Downtime? How long does is take to go back to that point in time?
- If you make a daily full and quarterly DIFFS you can lose 15 minutes of transactions at the most. Can these transactions be added manually again? What kind of transactions are they?
So many questions that this depends on.
What is worse? Taking 2 full hours to restore to a certain point in time (thereby losing 2 while gaining perhaps half an hour), or losing half an hour of data and have the organisation re-enter said data? Most often we find that organisations have shorter downtime (eventhough there's a loss of data) with the simple recovery model because restoring it is easy and often transactions can be resubmitted by the users themselves.
There is NO set of rules or laws that state that you should use either Simple or Full recovery model, thinking in that manner is shortsighted at best. A smart DBA checks each database and defines a recovery model for each database depending on it's use.
In practice, most databases with the Simple recovery model can be brought online far quicker than those with Full recovery models. Please do no take this shortsighted article on face value and think a little bit beyond shortsightedness. I've rated the article as 'awful', simply because it says that people SHOULD use the Full recovery model, it's a mindboggingly dumb statement. Sorry for my rude reaction, but my point should be made with a little bit of force behind it.
Thank you for your comments.
You are absolutely right. Each and every database environment is unique and the specific requirements for a robust DR strategy require that evaluation be performed on a case by case basis.
To emphasise this need, one of the closing points of the article reads:
“Database backups and SQL Server disaster recovery planning are detailed topics and require much more consideration, research and planning than are presented in this article alone.”
I would also like to highlight that designing a DR strategy is not the topic being presented by the article. Taken from the article:
“To summarise the key message here, in order to be able to restore your database to a particular point in time you "must" be using the Full Recovery Model.”
Once more, thank you for your feedback. It’s always good to see database professionals who are passionate about their work.