Problems with deleting older backup files

Question

Problems with deleting older backup files

Patrick Bohman

Grasshopper

Points: 14
More actions
April 26, 2006 at 9:42 am

#117404

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

David Manix Say Hey Kid Points: 685 More actions · Answer 2

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.

Patrick Bohman Grasshopper Points: 14 More actions · Answer 3

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 ...

David Manix Say Hey Kid Points: 685 More actions · Answer 4

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://www.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..

John Rowan SSC Guru Points: 56440 More actions · Answer 5

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.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Andrew Deren Mr or Mrs. 500 Points: 597 More actions · Answer 6

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?

John-316388 Newbie Points: 3 More actions · Answer 7

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