Maintenance Plan Clean Up Task Multiple subfolders

• acrutchley

  SSCrazy

  Points: 2117

  More actions

  May 8, 2014 at 8:55 am

  #291129

  Hi Team,

  I was wondering if there was any way of extending the cleanup tasks ability to delve further down than just the 1st level subfolder?

  My situation is as follows:

  I run hosting platform with many client SQL servers, the databases on each get backed up in the following manner:

  1 x weekly full

  1 x daily differential

  and 1 hourly transaction from 08:00 through to 20:00

  This is as our SLA's.

  The backups need to be kept online for 4 weeks and then archived to tape for x amount of months (depending on customer requirement).

  Each server does not have enough space to store 4 weeks of backups online, so we have purchased a disk array where the backups will be copied to throughout the day.

  The intended file structure where the backups will be stored will look something like:

  \driveletter\SQLBackups\servername\full\then the backup files

  \diff\then the backup files

  \trans\then the backup files

  \servername\full\then the backup files

  \diff\then the backup files

  \trans\then the backup files

  etc.....

  So basically we end up with a structure per server followed by the backup type, then followed by the backup files themselves.

  I would like to be able to setup a single maintenance cleanup task, which will point to the <SQLBackups> directory and delete all backup older than say 5 weeks, however this cause an issue as there are 2 following subfolders and the maintenance task only seems to allow 1st level subfolders (I think this is security protection thing).

  The easier option would be to change the folder structure, however this has been decided on by the powers that be and is very unlikely to change.

  If its not possible through a standard maintenance plan, could it be achieved via SSIS and running a package via SQL agent? ...... or any other suggestions welcome?

  Thank you in advance for any assistance.

  Adam 😉