February 11, 2014 at 8:34 pm
Comments posted to this topic are about the item How Often Do I Backup my Log?
February 12, 2014 at 4:07 am
Good editorial. Excellent article.
Fortunately, in my day to day life database backups and recovery have never been my responsibility (at least not since before I started using SQL Server in 1999 - newbie to some, old hand to others, ridiculous to all). Having said that, I think that this is such a fundamental that we all should have (at least) a reasonable knowledge. Of course, a DBA should understand this in depth (otherwise you will have Jeff to deal with).
This editorial is one of the few that comes across as not so much as a question to debate but a stern steering. Given what is said, I for one totally understand that.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 12, 2014 at 6:34 am
“The maximum amount of data that can be lost in the case of a disaster”
This statement may need a bit of an update, or an clause that assumes your only recovery option is via your log backups. If you are using 2012 HA or have replicated storage to your disaster system then your data loss may be lower, possibly seconds of loss.
February 12, 2014 at 7:56 am
How much data loss can you tolerate? That has been the question forever. It doesn't matter what platform I worked on, backup frequency and recovery options always boiled down to that one question. It makes it so much easier to determine backup and restore strategy when that question is answered.
Don't assume it will be none. There are systems/applications that can tolerate some data loss because the data can be re-entered or retrieved from some base document.
Getting yelled at has never been a concern for me. I always contact the user community and together we determine backup strategy. They sign off on it and are aware of any potential data loss and how to respond.
Tom
February 12, 2014 at 8:22 am
EricEyster (2/12/2014)
This statement may need a bit of an update, or an clause that assumes your only recovery option is via your log backups. If you are using 2012 HA or have replicated storage to your disaster system then your data loss may be lower, possibly seconds of loss.
Depends on the kind of disaster. Neither option you mention offer much protection against the 'oops, did I just drop that table on production?'. Depending on where the servers are situated, they may or may not offer protection against a complete loss of a building, or a data center or a city (floods, power outages, seismic events, tornado, hurricane, etc)
I would say, especially if you have some form of HA, your log backup frequency should be determined by maximum amount of data loss tolerable, as if you're resorting to backups in that situation then every other HA option implemented has already failed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 8:38 am
As Gail mentioned, disasters vary. It's easy to think of HA as protecting you from disaster, but it doesn't. Don't conflate the idea of HA with DR. They are separate.
February 12, 2014 at 9:11 am
"Tolerance for being yelled at" is a great way to put it. I can't think of a better one.
If the data is financial transactions, event registrations, or something else where every record dropped adds an extra person or company you are going to be dealing with during recovery, you might want to be stashing a separate log of that information somewhere else as well.
February 12, 2014 at 9:18 am
Steve Jones - SSC Editor (2/12/2014)
As Gail mentioned, disasters vary. It's easy to think of HA as protecting you from disaster, but it doesn't. Don't conflate the idea of HA with DR. They are separate.
Thanks for making that point Steve. HA <> DR
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 13, 2014 at 8:17 am
Steve Jones - SSC Editor (2/12/2014)
As Gail mentioned, disasters vary. It's easy to think of HA as protecting you from disaster, but it doesn't. Don't conflate the idea of HA with DR. They are separate.
Oh how true. I can tell you from experience that if a developer runs a script that truncates most of the main tables in production, HA and SAN replication does not protect you. And when I asked how often the transaction logs were backed up, I got the most wonderful answer, "What do you mean?"!!
February 13, 2014 at 8:31 am
Another question to always keep in mind is how long is do you want to keep the tran log/differential backups.
If you have a business that is doing a hundred new sales/transactions/entries per hour and someone does a mass delete that you know about twenty-five minutes later then you want to get as close as possible before the delete.
But if someone purged last year's record by mistake and you discover it two days later are you going to want to do a PIT recovery to two days ago and lose all the the current data?
Or are you going to do a parallel DB restore and find a way to move the old data across? And if it is a delivered app/db can you do that? That needs to be built in as part of DR planning process.
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply