Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 Maintenance Plan Cleanup Expand / Collapse
Author
Message
Posted Tuesday, October 16, 2007 6:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 5,872, Visits: 12,978
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?


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

Post #411222
Posted Tuesday, October 16, 2007 6:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 1,051, Visits: 1,123
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
Post #411261
Posted Tuesday, October 16, 2007 7:46 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 5,872, Visits: 12,978
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.


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

Post #411295
Posted Wednesday, October 17, 2007 4:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 1,051, Visits: 1,123
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
Post #411642
Posted Thursday, October 18, 2007 12:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:33 PM
Points: 328, Visits: 145
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
Post #412372
Posted Friday, October 19, 2007 2:48 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 5,872, Visits: 12,978
ok, thanks andy and 'deadwood'.

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


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

Post #412630
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse