Home / server / Questions / 191462
Accepted
lysdexic
Asked: 2010-10-16 10:34:59 +0800 CST

Sql server backups best practices. Too many files created?

  • 772

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

sql-server-2005 backup windows-server-2003 maintenance

1 Answers

  1. Best Answer

    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.

    • 3