Understanding Backup Types

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.