April 26, 2006 at 9:42 am
Hi all
Have an interesting problem; When creating a maintenance plan including backup of all user databases in there own folders the folowing problem: How do I automatically delete older *.bak and *.trn files within those folders?
April 26, 2006 at 10:20 am
There is an option in the Maintenance Plan Wizard that will allow you to specify backup retention. Setting this option will remove old files for you.
April 27, 2006 at 4:41 am
Personally I have not seen this option in the SQL 2005 "Maintenance Plan Wizard" but I have seen it in the "New Maintenance Plan" option instead. So I have been creating Maintenance Plans with the wizard since it is more like what was in SQL 2000 and then I create a "New Maintenance Plan"- a Cleanup Backup plan to purge out the old backup files.
In the "New Maintenance Plan" select the Maintenance Cleanup Task (on the left side). Then in the Design tab that comes up, double-click the task to configure it, browse to the folder holding backups, specify backup extension (may need more than one plan if multiple extensions), and set the backup retention in the File Age section. Click ok and Save Selected item under the File menu.
You can also do similar to your original maintenance plan by adding this "Maintenance Cleanup Task" directly to your "Wizard-created" maiantenance plan - but I thought what I described above was more straightforward to start with..
Hope it helps.
April 27, 2006 at 4:47 am
But does this work if the files resides in there own folders? The problem is that my customer has 30+ db's on the server and each database stores it's backup files in it's own folder and I dont want to create 30+ MP's or steps in one MP.
Thanks for the help so far ...
April 27, 2006 at 5:15 am
I hear you - that makes it more difficult.. without creating seperate cleanup steps for each or adding the step to each existing maintenance plan (and without creating entirely new maintenance plans using the 'new maintenance plan' option entirely) - the only other thing you could maybe do is code this..
Not sure your development background but there are some good examples in the scripts section of this site (resources, Scripts) that might get you started - for example, http://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=1627, that one uses the file system object and there would certainly be other ways like that - although they would have to be tweaked to have them go through all the different folders you have for all the different backups.. Some of the scripts in there will specify SQL 7/2000 but their methods should work universally since they are outside SQL itself and doing the cleanup another way.
Otherwise the only thing is to start plugging away one by one either adding new step/plan for each or adding this step to existing plan or creating new plans from scratch using the 'new maintenance plan' option rather than the 'wizard' - at least from what I found so far in SQL 2005..
April 27, 2006 at 8:50 am
My bad, I did not see that this was a 2005 post. You will need to use the Cleanup History task like David has suggested.
May 1, 2006 at 6:37 pm
I have the same problem. The files just stay there.
I intalled SP1 today hoping it would resolve the problem, but it does not.
The logs say the plan ran successfully, but it did not remove the old files.
I even tried running the command manually:
xp_delete_file
It says it complete succesfully, but the files still stay there.
How can I force to remove older files?
May 12, 2006 at 9:35 am
Like David mentioned add a "Maintenance Cleanup Task" to your maintenance plan. You will need to specify the directory to which backups are being made and the file extensions of the backup files. Also make sure to check the box beside "Include first-level subfolders".
Removing backups in subfolders was apparently a bug that was addressed in SP1.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=286137&SiteId=1&PageID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=336728&SiteID=1
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply