SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How Often Do I Backup my Log?


How Often Do I Backup my Log?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65633 Visits: 19118
Comments posted to this topic are about the item How Often Do I Backup my Log?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16937 Visits: 6534
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!!!
EricEyster
EricEyster
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 Visits: 520
“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.
OCTom
OCTom
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3177 Visits: 4152
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91093 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65633 Visits: 19118
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Megan Brooks
Megan Brooks
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 156
"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.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33606 Visits: 18560
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

Jim Youmans-439383
Jim Youmans-439383
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 541
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?"!!
Jim P.
Jim P.
SSC Eights!
SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)SSC Eights! (945 reputation)

Group: General Forum Members
Points: 945 Visits: 2215
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search