Share via

Funny SQL2005 Maintenance Features (posted by Paul)

  • Article

In SQL2000 maintenance plans if you want to backup databases there is an option to allow you to create a subfolder for each database that gets backed-up. And the same dialog is nice enough to ask if you want to remove backups older than x amount of days. It's pretty simple and everyone uses it and everyone's happy.

So you'd think in SQL2005 it would be a similar story, right? Sure enough if you create a Back Up Database Task it will also let you create a separate subdirectory for each database. However if you think that it will also cleanly remove old backups from these subdirectories you're sadly mistaken. In the Maintenance Cleanup Task it will indeed delete backups older than x days, but - and here's the kicker - not in subdirectories!

Yes, you read that right. The GUI will let you create a backup solution that it cannot maintain out of the box. I talked to the SQL team and the response was that it might be a feature added at a later time. Maybe. We'll see. So in the end you either have to change your strategy and (pick 1):

1) Have a single backup directory for all your databases.
2) Create a separate Maintenance Cleanup Task for each and every user directory that you have the Back Up Database Task create subdirectories for.
3) Create your own script to traverse the subdirectories and call xp_delete_file for each one.

If you only have a few databases options #1 and #2 aren't bad, but otherwise you're stuck with #3. The upside is that in SQL2005 it's not hard to write a CLR stored procedure to do this work and to call it from a maintenance plan.

Still, it seems strange that SQL2005 will let you create a backup strategy that it can't keep clean.

Paul

Comments