Pay Attention to Maintenance Cleanup Job Configuration (If you use Maintenance Plans)

Jonathan,

Most of the times this is not the final destination for these backup files and in case of a disaster you may not even have the three days of backups you are asking to keep: that disk may be also gone. Usually these backups are stored on some other permanent location, like a tape, and outside the data center. This value is just for the files that you can keep according to your available disk space.

Regards,

Benjamin

Jonathan

I would prefer explicity to specify cleanup configuration for database and log file backups, I mean ucheck first -level subfolders option. We have log file backups appended into the one file (WITH NOINIT)and hence there is no problem to delete them as well :-))

I agree Jonathan, most of us know that backing up to local disk as final destination is about as useful as not backing up at all.  However, "disasters" are not always synonymous with lost disk.  Backing up to local disk *and* combining that with deleting backups more than 3 days old when you are on a weekly full schedule makes it impossible to recover even if the disaster was that a few days ago someone deleted the wrong row.  You can't restore to a point in time (even on some other server just to recover that one row) if you've deleted the full backup because it was more than 3 days old.  

And yes, I too have seen this exact type of configuration.  In one situation, not only was the practice used because they didn't have enough room on the local disk to store a full backup plus a week's worth of logs (the "DBA" thought that log backups were enough!), they also scoffed at my brain dead suggestion to move backups to a different machine.  They didn't have enough space to store the backups on any other server either, and even if they were able to find room, they were worried about "flooding" their network with a backup (or copy of a local backup).  It seems they utterly missed the point of backups.

Uri, what browser or tool are you using to leave comments on this site?  The last few I've seen from you (on multiple individual blogs) have been posted three times.

I think it is also worth pointing out that the cleanup task is in no way automatically connected to a prior backup task (if such exists). Say you have a backup tsk to do backup for database "pubs" to a certain folder and you configurea cleanup task in same plan to remove files. The task will remove all bak files - not just for the pubs database.

Also, I typically have three cleanup tasks. One to remove bak files, another to remove trn file and yet another to remove maint plan report files.

You absolutely need to move SQL backups to another server or SAN at that location in addition to taking the files off-site. This allows you to recover from a local server hardware/disk failure much more quickly. It elminates the double hop of getting from tape.

Regarding maintenance plans, someday the SQL Team will catch on and put some real power into the maintenance plans. In the meantime, I also use scripts. I actually call a dynamic vbs script that uses both date and archive bit to determine when to delete backup files. If the archive bit is still set then it will not delete the file.

I would rather fill up the backup disk then delete a backup that never made it off-site. But you better have a solid process to catch that problem. In my case, all types of alarms start going off after the backup disk fills up to a certain point and/or the backup jobs fail.

But, YMMV.

Jonathan - I got so frustrated with this exact issue that I wrote my own CLR procedure to handle clean-up of backup files, including dependencies between them and whether they've been written out to backup media (DDR or tape). It's a bit of a sledgehammer, but it's workin' for me :-)

http://sqlblog.com/blogs/merrill_aldrich/archive/2009/07/21/hole-in-your-backup-sequence.aspx

Merril - Thanks for sharing the CLR approach. I'll have to give it a spin.

Jonathan,

Respectfully, I'd have to disagree with the fact that scripts are better than maintenance plans.  You can write bad scripts just as easily as you can write bad maintenance plans.  In either case, you have to pay attention to the details.  I'm sure that you are used to checking for the proper syntax, properties and errors in scripts more readily than you are used to checking for those same properties and errors in the plans - most DBAs are... It doesn't mean that the plans can't be just as effective if set up and configured properly.

Now, am I saying that you should use the plans over the scripts? Of course not.  But there is nothing wrong with using the plans as long as you are diligent about their configuration.

I wrote up another reason why I don't like maintenance plans. Not all of the options available with T-SQL scripts are incorporated into the maintenance plans and one of them is the ability to use BACKUP CHECKSUM. It may NOT be the single factor that will sway your decision.

http://sankarreddy.com/2010/02/another-reason-why-i-dont-like-maintenance-plans/

PingBack from http://www.johnsansom.com/index.php/2010/02/soemthing-for-the-weekend-sql-server-links-120210/

PingBack from http://www.pythian.com/news/8725/log-buffer-179-a-carnival-of-the-vanities-for-dbas/