September 21, 2009 at 12:26 am
Comments posted to this topic are about the item Part 2: Why you should be using the Full Recovery Model
September 21, 2009 at 8:35 am
I agree that the default recovery model should be Full. However there are cases where the simple model is more appropriate. For example, if its mostly large "batch" jobs that update the database, and losing a day of processing would only cause a small amount of harm to the organization, then the simple recovery model may be a lot easier to administer. Should you lose the database, its fairly easy to restore the latest backup and then re-run the batch jobs.
September 21, 2009 at 8:57 am
Nicole Garris (9/21/2009)
I agree that the default recovery model should be Full. However there are cases where the simple model is more appropriate. For example, if its mostly large "batch" jobs that update the database, and losing a day of processing would only cause a small amount of harm to the organization, then the simple recovery model may be a lot easier to administer. Should you lose the database, its fairly easy to restore the latest backup and then re-run the batch jobs.
Hi Nicole,
Thank you for your comments.
I agree. There are indeed certain types of database with workloads that are not necessarily suited to operation within the Full Recovery Model.
In addition to those you highlighted, other types of database that would not necessarily be suited to operation under the Full Recovery Model include Read Only databases and databases that experience discrete infrequent modifications.
The key point that the article is endeavouring to communicate is that if you need to ensure that you are able to recover your database to a specific “point in time” then you must be using the Full Recovery Model.
Cheers,
September 21, 2009 at 9:05 am
Thanks. I'm glad you posted your article, its much needed. Over and over again I see OLTP-oriented databases which are installed as Simple which really should be Full.
September 21, 2009 at 10:25 am
For those that use "simple" recovery model, when it comes to backups, does it matter if you are doing full backups or simply backing up the .mdf/.ldf files? Wouldn't both create the same effect?
September 21, 2009 at 10:40 am
Jason-181907 (9/21/2009)
For those that use "simple" recovery model, when it comes to backups, does it matter if you are doing full backups or simply backing up the .mdf/.ldf files? Wouldn't both create the same effect?
You cannot backup the mdf/ldf file while SQL Server is running. The files are locked and will not be backed up using normal backup utilities.
There are SAN based utilities that utilize VDI to 'freeze' SQL Server and then snap the volume where the databases reside. However, this is not the same as backing up the mdf/ldf.
And since there is no reason to shut down SQL Server every night - I don't see any way to back those files up, so using native SQL Server backups is the appropriate way to make sure you have good backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 21, 2009 at 3:19 pm
Jeffrey - thanks for the reply. Won't BackupExec or the other major backup products out there back up "open files"?
Jeffrey Williams-493691 (9/21/2009)
You cannot backup the mdf/ldf file while SQL Server is running. The files are locked and will not be backed up using normal backup utilities.
There are SAN based utilities that utilize VDI to 'freeze' SQL Server and then snap the volume where the databases reside. However, this is not the same as backing up the mdf/ldf.
And since there is no reason to shut down SQL Server every night - I don't see any way to back those files up, so using native SQL Server backups is the appropriate way to make sure you have good backups.
September 21, 2009 at 3:43 pm
If you would like the default recovery model to be Full, then change the recovery model on the model database.
September 21, 2009 at 3:51 pm
From the title of this article, I was expecting much more on why I should be using the Full Recovery Model. Instead, I got an introduction to data loss and an introduction to backups. The author didn't introduce "SQL Server Recover Models" [sic] until near the end of the article, just before "Final Thoughts". Even then, this section was mostly links to BOL or extracts from it.
I'm not suggesting that this is a bad or misleading article. However, I would definitely change the title to something more appropriate.
September 21, 2009 at 3:57 pm
No, BackupExec and other utilites like that won't backup SQL Server database files that are open. SQL Server locks the files and will not allow them to be backed up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 21, 2009 at 3:59 pm
... other types of database that would not necessarily be suited to operation under the Full Recovery Model include ... databases that experience discrete infrequent modifications.
I cannot agree with you here.
If you are on Simple, and your log was truncated this morning, and today you have had "discrete infrequent modifications", aren't you going to want to recover them?
Frequency of modification has nothing to do with the choice of recovery model. You should be more concerned with data loss, of any size.
In other words, a database with infrequent modifications could be an excellent candidate for the Full Recovery Model.
September 21, 2009 at 6:51 pm
In general, a database should be in full backup mode for precisely the reasons described here. There are advantages to using the Simple backup model.
The prime one being less concern about the transaction log growing or worry about them filling up. They can still grow of course (assuming autogrowth is on) in simple, but this will be a less frequent event under normal usage patterns using simple than using full, and there is no need to trake the time to truncate those logs.
Also, certain types of transactions are generally faster under a simple model.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
September 23, 2009 at 8:55 am
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.
September 23, 2009 at 10:01 am
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.
September 23, 2009 at 10:16 am
Festeron (9/21/2009)
... other types of database that would not necessarily be suited to operation under the Full Recovery Model include ... databases that experience discrete infrequent modifications.
I cannot agree with you here.
If you are on Simple, and your log was truncated this morning, and today you have had "discrete infrequent modifications", aren't you going to want to recover them?
Frequency of modification has nothing to do with the choice of recovery model. You should be more concerned with data loss, of any size.
In other words, a database with infrequent modifications could be an excellent candidate for the Full Recovery Model.
Hi Festeron,
Thank you for your comments.
There is no one solution fits all, perhaps I should have made my original point clearer, although I'm pretty sure I did NOT say "All databases with discrete infrequent modification should use the SIMPLE Recovery Model" 😀
To re-iterate my prior statement, with emphasis:
In addition to those you highlighted, other types of database that would not necessarily be suited to operation under the Full Recovery Model include Read Only databases and databases that experience discrete infrequent modifications.
So the point I was trying to be make is that using the FULL recovery model for static (read only) or relatively static databases might be overkill in some scenarios. For example, if a database is only updated say by a batch process once a month, the SIMPLE recovery model may be a more appropriate choice. Provided a FULL database backup is taken after the batch process, the database can be restored to it's current state at any point within the month.
Thanks again for your comments!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply