Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm in the midst of changing my company's SQL Server backup practice. We use to create full backups weekly and daily differential backups overnight. The issue is that as these full backups grow, it won't be possible to FTP Gigabytes of data weekly.

As an alternative, I was thinking of keeping a single full backup and perform log backups + ftp hourly. Some concerns have risen such as

  • Are these log backups reliable?
  • If one of the log backups fail, will it not cause all future backups to fail?

Any opinions are highly appreciated!

share|improve this question

closed as primarily opinion-based by Bryan, Falcon Momot, Sirex, mdpc, Ward Jul 29 '13 at 0:43

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

Your data backup/retention/recovery plan should be driven by the business need and mandate, not by whether or not you can conveniently upload the data to a remote location via FTP. – joeqwerty Feb 21 '12 at 0:18
up vote 0 down vote accepted

First of all, ask yourself what the purpose of having an indefinite backlog of all transactions ever submitted to the database would be. Is it really important that you can restore the database as it was 7 months and 8 days ago?

The most common reason for backing up a database is disaster recovery. If your database fails, for whatever reason, you wouldn't restore from a backup taken a year ago. You would restore the database to the most recent backup available.

If you agree, why not employ a backup plan in which you take:

  1. Weekly full backups
  2. Daily differential backups (for easement of restore operations)
  3. Hourly log backups (for up-to-date point-in-time restore ability)

Lets say you take a full backup Sunday at 00:05, and all other days a differential backup at the same time.

If someone accidentally drops all table at thursday around noon, you would only need to restore: The full backup from sunday, the differential backup from thursday (at 00:05), and 11 hours of log backups.

With this model, you only ever need 1 (one!) week of backlog to recover from a disaster.

Every sunday, when the full backup is complete, you can simply discard all backups from the previous week.

This of course, only applies in a scenario in which the purpose for backup is disaster recovery

share|improve this answer
Thanks for the quick reply. The purpose of my backup is for disaster recovery. The problem with the method you explained is that I will have to FTP the full backup weekly. This may be impractical if your database is 1 TB of data. – user1034912 Feb 21 '12 at 0:33
@user1034912 Yes, that is very impractical, but why would you use FTP for this? Why not a proper backup agent like Networker, Exec or TSM, capable of backing up your client(sql-server) remotely? – Mathias R. Jessen Feb 21 '12 at 0:50
Thanks, I never knew any of these backup agents. Are there any free ones? – user1034912 Feb 21 '12 at 1:12
If you run a serious business with a terabyte of valuable information, that you sincerely want to protect, I have a hard time believing that you are not willing to invest in supported backup software. That is my most honest opinion :-) – Mathias R. Jessen Feb 21 '12 at 1:20
Mathias: I agree. I'd hate to have to explain to the boss that we don't have recent/good backups because it was too much to FTP or because it was too expensive. – joeqwerty Feb 21 '12 at 1:52

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