I want to run daily backups for a set of databases to disk such that I always have 3 days’ worth of backups kept for each database, with the oldest backup being deleted or overwritten each day. I set up a maintenance plan and set the expiry for the backup set to 3 days. The system uses a timestamp for each file (which I like) but older files are not removed and just build up in the directory, so I'm not sure what expiry means. Is there a way to achieve what I am looking for without having to write re a separate script to clean up the old backups?
Ola Hallengren's excellent scripts provide solutions for database maintenance including backups - the scripts have won some awards in the last year, and are increasingly referenced by SQL Server luminaries. I've been using them for about a year - I love them because they are highly configurable - for backups, you define the retention period in the job in hours. The retention period only kicks in if you actually have refresh backups.
How you run them? Expiration does not delete them. If you set that up with a maintenance plan, the maintenance plan has a separate action for cleaning up old files.
Expiration only renders the backup unusable, it does not delete the file. This makes sense - a file can not self-destruct, it needs something deleting it.
In the maintenance plan there is another task you can add:
Maintenance cleanup task. You can add this task to the current plan to cleanup files older than 3 days. I find that this task is more prompt than the expire date in the backup task.
One more way to achieve this is to use forfiles.exe to delete files older than x days. We use LiteSpeed for backups and for some reason, the maintenance plan's cleanup task doesn't delete these files. I created a SQL Agent job that executes a bat file which uses forfiles .
forfiles -p "E:\your\backup_drive" -s -m *.* -d -number of days -c "cmd /c del @path"
If you are comfortable with VB script, mssqltips has an article on how to do it.
Relevant SO question here