Sql-server – Understanding Backup Types

backupsql server

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?

Best Answer

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.
Related Question