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.