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.