Sign up ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I know this has been asked before, but I'm trying to understand this better.

I traditionally used Backup exec to backup sql servers, but I now have two sql servers that are off-site and of course I don't want to back them up across the vpn. I have created maintenance plans for each of them. First I did a full backup, then changed the plan to do differential. I do not need to back up transaction logs for these servers because of the nature of the databases, at least not yet.

My question is this, I thought the differential backups would simply append the latest differential. But it's creating new files every night. If some of the databases don't change everyday, then I don't want all of these duplicate backup files.

Do I just need to set expiration of these differential files, will that actually delete the expired files?

Basically I'm just trying to come up with a solution to just have one full backup, and daily backups after that.

I'm reading more and more about it, but not really finding the right answer on google, and waiting for my SQL book to come in.

Am I missing anything?

Thanks to anyone who reads or responds, I appreciate it.

SQL2005, Server 2003, 32bit/64bit

share|improve this question

1 Answer 1

up vote 3 down vote accepted

You should set up maintenance plans to do 3 things:

  • Weekly Full backup
  • Daily differential backup
  • Clean up old backups (use the Maintenance Cleanup Task item)

Expiration does not refer to how long files are kept. Also, since you aren't using transaction log backups, make sure you have the database recovery model set to Simple.

share|improve this answer
That's pretty much what I needed to hear. Thanks! However now I'm finding out that some of the databases have the recovery model set to full and some are set to simple. The devs told me they were all "simple". Am I going to have to back these databases up separately or should I try and change the ones that say full to simple. (Of course I'll make sure they can be set to simple). –  lysdexic Oct 15 '10 at 19:01
If you aren't doing transaction log backups, you'll want to change them to Simple mode. Otherwise, the logs will continue to grow until they hit their configured maximum, or fill the disk, whichever comes first. –  phoebus Oct 15 '10 at 19:42
Excellent, thanks again! –  lysdexic Oct 15 '10 at 20:08

Your Answer


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.