I am having something on my Sql server that I need some help with.
Sql Server 2008 R2 Standard.
Windows Server 2008 R2
I have a maintenance plan running and one of the tasks for the maintenance plan is to backup the database onto a locally attached disk. I would like to have the maintenance plan create a new file for each backup so I can delete them individually (or move them individually) as I like.
Currently, I have "create a backup file for every database" checked. Also, I have it selected that the backup set will expire after 14 days.
The problem is that the disk is filling up. The disk fills up and then future backups fail until I go it and delete the unnecessary (oldes) backups.
I think I can fix this by deselecting "create a backup file for every database". But, I really like the idea of creating seperate files for each night's backup.
Is there a way for Sql to automatically delete .bak files that are more than 14 days old? Do I have to run them all into the same file to get this functionality?
If I ran every night's backup into the same file, would it automatically overwrite data that is only 14 days old or would I still have the same problem with it filling the disk?
Any other suggestions on this?