SQL 2005 Maintenance Plan Cleanup

Question

SQL 2005 Maintenance Plan Cleanup

george sibbald

SSC Guru

Points: 104210
More actions
October 16, 2007 at 6:05 am

#122035

SQl 2005 standard edition SP2 GDR2 (9.00.3054)
This is a consolidated server. I backup to a set directory with a sub-directory for each database. For simplicity(!) maintenance plans are used to backup databases. A maintenence plan may backup one database or multiple databases (eg the system dbs are in one plan).
I want to cycle out old backups, so I add a maintenance cleanup task to delete older backups, I want to keep some backups longer than others. For this I specify option 'search folder and delete files based on an extension' and where the plan deals with more than one database specify the set database backup directory plus the 'include first level subfolders' button.
AAaaaargh! old backups are deleted from ALL the sub-directories not just the ones created as part of this particular maintenance plan!
Surely this cannot be correct. Is this really what microsoft intended or Have I missed something or is this another maintenance plan bug I thought were all fixed by GDR2?
---------------------------------------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 1

I think the cleanup is working as planned. Whether you (we) agree with the design is another matter! Having tripped over this before, I now think of the cleanup task as a pretty dumb shell script, that searches for all files with the specified file extension, up to one subfolder deep, and deletes all old files.

The simplest workaround I know is to separate out your backups into folders (at the same level), and then create a cleanup task for each folder. Unfortunately this involves creating multiple cleanup tasks. Still, at least you can see what is happening.

Andy

george sibbald SSC Guru Points: 104210 More actions · Answer 2

Andy,

thanks. yes it is pretty dumb, especially as SQL2000 could handle any requirements fine. I will have to layout my backups and maintenance plans to work around this foible, though hopefully MS will fix this in the future.

I put it down to using SSIS for maintenance plans, which I have never liked.

---------------------------------------------------------------------

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 3

The addition of the include first level sub-folders option after SP1 (I think that's when it appeared) clearly shows that this function of the Maintenance Plan was a big let down for admins. Still, I don't remember it working perfectly in 2000 either....

Andy

Deadwood Mr or Mrs. 500 Points: 512 More actions · Answer 4

Hi...

To prevent backups from deleting, you could always change the backupextension for each maintenaince plan and deletion plan. Make one plan ba1, another ba2, etc... and set the delete task to the same..

However, there is not much we can do about it, so we just gotta work with what we got.

Cheers

Thanks

george sibbald SSC Guru Points: 104210 More actions · Answer 5

ok, thanks andy and 'deadwood'.

As deadwood says, work with what we got (or around it).

---------------------------------------------------------------------