SQL SERVER number of .bak files created
I am wondering where (if possible) you can configure the number of backups, for a particular SQL Server database, are kept within the system backups folder. i.e. it should “clean up” old backups automatically (if possible).
Furthermore, I have a VS2010 Database project, and I can specify that I want backups to be created upon deployment, but again I was hoping there was a way to control HOW many backups it will create, and if possible to clean up old backups.
I know its a loaded question, but there must be an easy way to do this?
2 Solutions collect form web for “SQL SERVER number of .bak files created”
Controlling when and how many backups will be created, and when the old backups are deleted is easy when you backup your databases with SQL Server’s Maintenance Plans.
There is a task named “Maintenance Cleanup Task” which you can set up to delete all backups of a certain database older than X.
Here is a blog post that describes a common gotcha, but also shows the Maintenance Cleanup Task with the necessary settings.
As Christian said, maintenance plans don’t support your scenario. And server-side code (TSQL or CLR procedures) often becomes very awkward when you need to interact with the filesystem.
So this is probably easiest to do at the OS level. Back up each database to a separate folder using a maintenance plan or some other method, then write a script in Perl, PowerShell or whatever you like that deletes the oldest file(s) from the folder when there are more than 3 files present. You can schedule it to run once per hour, day, week or whatever fits your release schedule.