Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have set up a SQL Server Agent job to execute a backup every night at 3am. The job appends the backup to an existing backup file, and sets the expiry of the backup to expire in 7 days time.

It's been running for several weeks, but the backup file is becoming very large, and if I look at the contents of the file (by clicking Contents in the backup config screen), I can see that it has every backup from when I started the job, and the expired backups are not being overwritten by the new backups.

I created the job in management studio, by right clicking the database, selecting tasks > backup, and then setting the following options:

  • Backup type: Full
  • Backup component: Database
  • Backup set will expire: After 7 days
  • Destination: Disk (then selected a file)
  • Overwrite Media: Back up to the existing media set, Append to the existing backup set

I then used the Script to new query window, and copied the command to a step in the agent job.

I'm using SQL Server 2005.

How can I configure this to overwrite old expired backups in the backup file, rather than keeping a copy of every backup? I just want a single file that always contains the last 7 days of backups.

share|improve this question
up vote 2 down vote accepted

I don't know if it will do that, or not, but you did not mention checking the option:

Check media set name and backup set expiration

It would seem like this would be required too. You have to keep in mind that SQL's backup command is not intended to be as full-functioned as a commercial backup software, and it just cares about allowing you to make ANY backup. It is possible that it can't do it exactly the way you want.

share|improve this answer

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.