Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

migrated from stackoverflow.com Jan 24 '13 at 16:50

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

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.

share|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
add comment

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

share|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
    
Thanks for your help. –  Tom Jan 24 '13 at 21:43
    
@Tom You're welcome –  Carol Baker West Jan 24 '13 at 22:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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