2

I created a maintenance plan to backup all user databases (full) and set them to expire after three days.

This has proved to be cost inefficient in regards to disk space so I want to switch to differential backups.

I then created a maintenance plan to backup all user databases (differential) and set them to expire after seven days.

I had thought the first time this job ran that the first backup would have to be full but it's not, it's significantly less than what a full backup should be.

I thought it would work like this:

Day 1: Full

Day 2-7: Differential

Then on day one it would do another full backup and start all over.

Am I just misunderstanding how differential backups work?

I mean what did the plan back up differentially if it didn't do a full backup first?

Thanks!

|improve this question
5

Differential backups include data that has changed since the last full backup (ignoring any full backups taken with the COPY_ONLY option).

If you take a differential backup immediately after a full backup, it will be very small, as little (or no) data will have changed. As time goes on, the differential backups will become larger and larger, until you do another full backup, which resets the differential change map.

Note that you must still have the last full backup taken prior to the differential backup, or the differential backup will be useless.

|improve this answer
  • Ah ok. So what I should do is create a job that does a full backup once a week setting that backup to expire after a week and then a job to do differentials the other six days? – Tom Jan 24 '13 at 19:03
  • 2
    Yup. Then when you restore the database, you would restore the full backup using WITH NORECOVERY, and then restore only the most recent differential backup (WITH NORECOVERY if you plan to apply log backups after that). – db2 Jan 24 '13 at 19:46
  • 1
    @Tom: continuing db2's idea, if you don't use (as it seems) log backups, change the recovery model of your databases to Simple. It will save you some space and some headaches in the future. – Marian Jan 24 '13 at 20:31
3

It's a good plan - 1 full db backup a week, daily differential backups

But, you have to create a job that will create FULL backups, if you've specified differential backups in a job, it will create only differential backups, not full ones

As db2 said - differential database backups are cumulative. They contain all transactions since the last full database backup, not since the last differential database backup, which seems to be a common misconception

|improve this answer
  • Copy from above: Ah ok. So what I should do is create a job that does a full backup once a week setting that backup to expire after a week and then a job to do differentials the other six days? – Tom Jan 24 '13 at 19:04
  • 1
    @Tom Yes, you must create these 2 jobs – Carol Baker West Jan 24 '13 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.