Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have 15 customer databases that have minimum replication at different sites. I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip. We recently had a hard drive crash at 6PM which means we lost all data in the database from 2AM - 6PM that day. How can I back-up our databases throughout the day without compromising the functionality of the database during the day? We do over 1,000 transactions daily so we are constantly writing to the database. My current method is not realistic for hourly back-ups as performance would suffer on the machine.

share|improve this question
add comment

migrated from stackoverflow.com May 8 '13 at 14:02

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 6 down vote accepted

I am noting the other answers here, but I don't believe any of them (at this moment) is a complete answer / solution to your problem. Yes, you can backup to a remote location, but that is not always the best way to do it. Backing up to a network location can be murderous to your bandwidth.

Transaction Log backups is very important and is the right way to go, and they can be off-setted with differentials. But there is usually some theory and planning that needs to go into your implementation thereof.

Differential and Transaction Log backups should cause a lot less performance issue for you, as the backups are a lot smaller. Differential Backups only contain changes made since the last full backup, and Transaction Log Backups only contain transactions done since the last full, differential or transaction log backup (normally just a few MBs).

Based on the details you have provided, there are over 1000 transactions done per day. On a normal 8 hour work day, this calculates to just over 2 transactions a minute.

Before you implement any backup plan, you need to determine (as a business) what your Recovery Time Objective (RTO) and Recovery Point Objective (RPO) is.

RTO relates to the maximum amount of time you are willing to lose during a disaster, to restore normal operations.

RPO relates to the maximum amount of data that you willing to lose during a disaster.

In most cases, the lower the RTO, the higher the RPO and vice versa.

With the metrics above, you should then decide what the best backup plan is to implement.

For e.g.:

PLAN A: (Low RPO, i.e. minimal data loss)

  • Daily Full Backups
  • 4 Hourly Differential Backups
  • Transaction Log Backups every 10 minutes

    No more than 10 minutes of data will be lost, but the restoration process might be complexed and therefore time-consuming (close to 30 backup files to restore from)

PLAN B: (Low RTO, i.e. minimal downtime)

  • Daily Full Backups
  • 12 Hourly Differential Backups
  • 4 Hourly Transaction Log Backups

    Up to 4 hours of data could be lost, but the restoration process would be faster, with only a max of 4 files to restore

Importantly, you should note that if you back up your databases to the same drive as where your data is, and you have a catastrophic drive loss, you are likely to lose the backups too. Always have your backups on a separate drive from data and logs.

Suggestions to improve / edit this answer is welcome!

share|improve this answer
    
That's a good piece of answer! Nicely done! –  Marian May 9 '13 at 8:24
    
This is fantastic. Thanks! –  Shmewnix May 13 '13 at 15:30
add comment

Take a look at either Transaction Log Backups, or Differential Backups. Neither one is a "full" backup, which should mean that it doesn't affect your day operation as much.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.