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 recently started a role as a junior DBA... Yesterday I created a new database for a co-worker and my boss asked me to make sure we get a backup plan on it asap.

I didn't think much about it, went into the agent, created a job that took a full backup every night @ 1AM. Being proud of myself I went to bed and didn't think anymore about it. This morning I started thinking that was not "Good enough" - if the database died at 12AM, then they would lose almost 23 hours of data, which would probably get me fired. :)

So to make sure I understand this - I think I need to do Differential Backups in addition to my full backup.. After looking at this tutorial, I had a few questions:

  1. Would I just go into the agent and add a job that happens at every hour (for example) that creates a differential backup?
  2. If my understanding is correct, that would backup the Transactional log every hour until 1AM when it does a Full backup, then it would "Reset" the T-Log and start over again from the next day, so at the most would be 1 hour of data loss - Is that correct?
  3. So in the end, I would have 2 jobs on the agent, one that fires off every day @ 1AM to do a "FULL" backup, and one that fires off every hour that does a differential backup?
share|improve this question

migrated from stackoverflow.com Mar 18 at 21:27

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

1  
There's another reason this is not good enough: You have not tested a restore. You don't know yet whether it is even possible to restore. google.com/… –  usr Mar 18 at 18:47
2  
Before designing a backup strategy, you need to find out from the business - How much data loss is acceptable ? Then you can go about carving out FULL, DIFF and T-log backups. Also, check out Ola's Backup Solution. Its free and well tested in community. –  Kin Mar 18 at 21:44
2  
Please read through the answers here. –  Aaron Bertrand Mar 19 at 3:08
1  
Definitely consult with the business. You should have both an acceptable service restore window (which affects more infrastructure than just the database itself) and an acceptable data loss window. These will vary greatly depending on application and client: some of ours have a 4 hour restore window with maximum 24 hour data loss, in which case a daily tested backup is enough but more won't hurt, some are 15 minutes and an hour respectively. Some applications have finer requirements than that. Also consider both offsite and offline backups for disaster recovery purposes. –  David Spillett Mar 19 at 9:43

1 Answer 1

up vote 1 down vote accepted

Kin is correct to point you to Ola Hallengren's backup and maintenance solution.

It sounds like you're new, so also consider looking into:

Aaron Bertrand is correct to point you to Why Does the Transaction Log Keep Growing or Run Out of Space?.

Usr is correct; until you test restores, assume your backups are worthless.

The commenters are also correct, you must consult with the business.

In more detail, you need:

  • RPO: Recovery Point Objective. How much data loss is acceptable - i.e. restore back to within an hour before the loss? A day?
  • RTO: Recovery Time Objective. How long it takes to get the system back up again.
  • At least a skeleton DR plan, in particular, what types of "disasters" are your RPO and RTO good for?
    • corrupt database
    • hard drive crash
    • multiple drive crash resulting in RAIDset loss
    • accidental file deletion
    • server dropped during move
    • server stolen
    • building burned down
    • regional natural disaster (large hurricanes, earthquakes, typhoons, tsunami, major volcanic eruptions, major flooding)
  • Budget
    • After you get this, you can go back and renegotiate the previous points.

As far as a general backup plan, I would start with considering:

  • Search for anywhere someone's truncating the log... and stop that! You don't want to breaking your log chain!
    • Likewise if you're switching in and out of SIMPLE recovery model.
  • Figure out where your backups need to go; backing up to the same disk spindles that hold your databases or logs is pretty pointless by itself; lose the disks, and you lose both the active database and the backups all in one fell swoop.
  • In all cases, set CHECKSUM on.
  • Full backups for everything, regularly.
    • Maybe this is daily, maybe it's weekly, maybe it's biweekly, maybe even monthly.
    • NOTE: Keeping more than one around is useful; for FULL (and BULK-LOGGED if there are no bulk-logged changes) recovery model databases, you can skip a corrupt full backup if you have a prior full backup and an unbroken log chain.
  • Full backups are the ONLY backup allowed for Master.
    • Timing on this must be at least as frequent as your RPO.
  • Don't forget to back up msdb too, and you might as well toss model in.
    • yes, model. Sometimes it has user defined types, etc.
  • Differential backups on SIMPLE mode databases
    • Timing on this must be at least as frequent as your RPO.
  • OPTIONAL: Differential backups on FULL and BULK-LOGGED databases
    • these can allow you to have faster restores
    • these can also allow you to "skip over" damaged/lost/corrupt transaction log backups that are BETWEEN your full backup and a DIFFERENTIAL, after which you can continue restoring transaction logs as long as your log chain is unbroken from that point.
  • Log backups on all FULL and BULK-LOGGED database.
    • Timing on this must be at least as frequent as your RPO.
    • This is MANDATORY in order to keep t-log size down
  • Run test restores; it doesn't matter if they're on the same server or a different server, just run them.
  • Who has access to the backups
  • Encryption of the backups
    • Key management of the encryption
  • Offsite storage
    • And how that affects RTO
      • during the "larger" disasters; even so much as a blizzard or mudslide can add hours or days to this.
share|improve this answer

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.