I'm trying to convince a client that their SQL Server backup strategy is insufficient. Long story short is that their current backup "strategy" is a weekly full backup followed by daily transaction log backups. I have already advised the client that, due to the size of the database, they should switch to a daily full backup strategy accompanied by multiple daily transaction log backups or at least add a daily differential backup to what they already have in place. However, they are floundering around with this because it will require much more drive space than they already have. So, my question is - what are potential problems or risks that could be presented for a backup strategy that is only a weekly full backup and daily transaction log backups? Is it mainly that it may take hours or days to restore the database because the t-log restore has to re-run each transaction after the full backup is restored?

Here are the specs of the database for sizing and activity context:

  • Database Space Used: about 12.5GB
  • A weeks worth of transaction log backups: 7 TRN files with a sum total size of about 400 MB
  • A weekly post-reindex transaction log backup: about 3GB

Their backup strategy shouldn't be driven by your opinion, it should be driven by their recovery point objective and recovery time objective. Have that discussion with the client and then implement a backup strategy that meets those objectives.

That being said, I'd be very surprised if a restore of the database and transaction logs of the quantity and size you mentioned took more than some number of minutes. It certainly wouldn't take hours or days.

| improve this answer | |
  • I have taken note of your first paragraph. That first sentence is something I haven't thought of before (I'm a SQL dev, but getting into some light DBA work for side projects in my current position). Can you recommend some resources pertaining to this such as how to discuss this with the stake holders? I've already read some Brent Ozar articles that mention how all clients say zero data loss is the only option, but in reality is basically nearly impossible to obtain or cost prohibitive. – PEBKAC Jan 21 '16 at 14:14

My biggest concern is actually the transaction log backups. If they were to lose the database five minutes before their scheduled t-log backups they could lose almost 24 hours worth of data.

More frequent log backups shouldn't take that much space--there won't be more transactions. It should just allow a better restore point.

(No, it will not take days to run a restore on a 12.5GB database.)

The main advantage of differentials in that situation is that you might begin to find the number of log backup files unwieldy. If they're running weekly fulls and hourly transaction log backups, that's 24 files a day. If they lost their database five minutes before their full and fifty-five minutes after their last log backup, they could lose fifty-five minutes and would need 167 log backups (I'm assuming these are different files). With a nightly differential, they'd only need 23.

(If they decide that 55 minutes is too much to lose, back up the transaction log more frequently and multiply the number of files accordingly.)

| improve this answer | |
  • The thing about it is that this is a perfectly valid strategy. Of course, I would argue against having the database in full recovery model and move to taking a full weekly and a differential on the other days, but as another answer states here it's all driven by RPO and RTO. – Ben Thul Jan 21 '16 at 2:44
  • So, I'm seeing two responses here that say time to restore shouldn't be an issue and the main factor would end up being the number of files that need to be restored. Am I understanding this correctly? – PEBKAC Jan 21 '16 at 3:00
  • Concerning " If they lost their database five minutes before their full and fifty-five minutes after their last log backup, they'd lose fifty-five minutes", Is this assuming that the log tail is unrecoverable? – PEBKAC Jan 21 '16 at 14:17
  • Yes, that was assuming the log tail was toast. I should have said "they could" instead of "they'd." And the main issue should be how much data they're willing to lose. Like joeqwerty said, RPO and RTO. – Katherine Villyard Jan 21 '16 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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