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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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