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?