Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How Often Do I Backup my Log? Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 8:34 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:03 PM
Points: 31,276, Visits: 15,728
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
Post #1540538
Posted Wednesday, February 12, 2014 4:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:23 PM
Points: 5,734, Visits: 3,644
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!!!
Post #1540618
Posted Wednesday, February 12, 2014 6:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
“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.
Post #1540696
Posted Wednesday, February 12, 2014 7:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 2,625, Visits: 4,021
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
Post #1540745
Posted Wednesday, February 12, 2014 8:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,390, Visits: 36,823
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 2008, MVP
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

Post #1540776
Posted Wednesday, February 12, 2014 8:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:03 PM
Points: 31,276, Visits: 15,728
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
Post #1540793
Posted Wednesday, February 12, 2014 9:11 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:10 PM
Points: 23, Visits: 108
"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.
Post #1540835
Posted Wednesday, February 12, 2014 9:18 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:03 PM
Points: 17,941, Visits: 15,930
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
Post #1540847
Posted Thursday, February 13, 2014 8:17 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:57 PM
Points: 82, Visits: 303
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?"!!
Post #1541228
Posted Thursday, February 13, 2014 8:31 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:42 PM
Points: 635, Visits: 2,215
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.
Post #1541241
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse