Understanding Backup Types

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:

• Would I just go into the agent and add a job that happens at every hour (for example) that creates a differential backup?

• 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?

• 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?

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:

• SQLServerCentral's Top 6 Myths of Transaction Logs.

• Paul Randall's Transaction Log blog entries

• Paul Randall's Backup and Restore blog entries

• Mandatory: Read A SQL Server DBA myth a day: (30/30) backup myths

• Kimberly Tripp's Transaction Log blog entries

• Mandatory: Read Transaction Log VLFs – too many or too few?

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.

StackExchange Database Administrators Q#61187, answer score: 6