2

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

1 Answer 1

3

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.

2
  • 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, 2010 at 19:01
  • 1
    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, 2010 at 19:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .