1

I have a SQL Server located in a colo center. Currently, our database is about 100Gb. Log files average about 500Mb/day, and differential backups grow by a bit less than that.

My current disaster recovery plan is: have a full backup elsewhere, take log backups every ten minutes, diffs every night. I copy the logs and diffs off to S3, so, except for the tail, they're not on the machine in case it goes completely tits up.

I can restore the full backup elsewhere, and I check occasionally to make sure I can restore the entire log chain. Usually, I'd just restore "full -> latest diffs -> logs after diff", but I do run the full set occasionally, just to make sure I can.

My question is, how often do people actually do the full backup? I can't see it being worthwhile to try and jam 100Gb into S3 every day. I'm not even sure I'd do it every week. Is there a "best practices" for this, or does it all just boil down to my own ability/desire to buy bandwidth/storage, vs. a longer recovery time if I have to apply days (or weeks) of logs.

I guess the real risk is that some log file in the chain gets corrupted, and then I'm totally screwed.

Eric H.
  • 133
  • 3

2 Answers2

2

Best practice would suggest that your backup method and type meet the business DR/BCP objective.

What are the objectives of your DR/BCP?

joeqwerty
  • 109,901
  • 6
  • 81
  • 172
  • I guess I'm really asking "has anyone ever had a problem with a log file getting corrupted". Because that seems to be my weak link. – Eric H. May 05 '11 at 14:03
1

Our schedule is the same as yours. Tlogs every 15minutes to 1 hours (depending on the db), Diffs at night, and fulls during the weekend.

It all depends on the SLA of the database that you are serving, and how much downtime everyone is okay with having.

RateControl
  • 1,207
  • 9
  • 20
  • right.... my question is, can I get away with ditching the "fulls during the weekend". I mean, at 100Gb, and assuming the diffs grow .5Gb/day, it's going to take 200 days (which, since we're a monday -> friday type business, is the better part of a year) before moving around a full is faster than a diff. – Eric H. May 05 '11 at 14:08
  • In theory yes, you can do away with the fulls, if you have absolute confidence in your transaction logs and diff backups. You can perhaps do them once every two weeks? once a month? One the flip side the DBA in me says "oh hell no, you must do full backups, what if A, B and C happen". I haven't had any corruption of my backup files...yet. It can and will happen, what you and others will have to weigh is the risk of not doing weekend fulls. Ask others involved, so when "stuff" hits the fan, you're not the only on to blame. – RateControl May 05 '11 at 14:56