SQL Backup Cleanups(or Did your Moneybags Make it to Tape?)
I have long hated maintenance plans. First was because of the cryptic error messages when they fail. However, DBAs before me clued me into the fact they clean up files whether or not they make it to tape. The cleanup process is oblivious to the tape backup. This is really important. It can be catastrophic for this to happen. How many days do you keep on disk? One, two, three, seven days? Do you manage the tape backup as well? What if you have to restore from tape longer than that but the windows\backup admin was out on vacation while the tape backup was failing? I submit to you that it is better to fill up the backup and log drives before you delete non-archived backups.
There are several ways you can guarantee backups make it to tape before you delete them. First, you could check the archive bit.
For example:
del /a-a *.trn *.bak *.dff
Of course, you may wish to do it from powershell, vbscript or xp_cmdshell so you can only delete files older than a certain date. You may want additional logic if you need to keep a weekly full, a nightly diff and 24 hours of tlogs ON DISK.
Most backup software also allows you to run a post job script where you could clean up backups. I can think of horrific scenarios where it would still delete the “money bags” so I would stick with the windows file system attribute. If there is a bug there, it will be SEV A and lot of people will run into it.
