I wrote this script out of necessity at work. Typically in our production environment, we do regular full backups to a drive. Windows automatically sets archive bits on when a file is created. During the day, we then have a third party archiving software package kick in and copy these backups to tape for long-term (3 or 7 year storage) after which it clears the Archive bit.
However, one particular server is running out of space. We try to keep one backup online only but there is sometimes a failure in between the backups and the archiving. If the backup overlaps with the archiving software, the archiver may not be able to backup a file that is still being written to and an extra backup is kept. Until we get more hard drive space, we needed a way to delete old files which SQL Server couldn't do because the Archiving had locked the file, but we also needed to make sure we didn't delete any old files that had not been archived yet.
I created this script that functions as a generic delete old files script (path, extension, and age) or has a check archive argument that checks the archive bit. Even if a file has the correct extension and is older than you need, if the archive bit is set (attribute 'A' on the file), the deletion will not occur. You can easily test this by making some dummy files in your c:\temp directory. Create two or three files with a .bak extension. Right click on one of them --> properties --> click Advanced --> clear the File is ready for archiving box and save.
Next, from within sql server on the same machine, run the script as:
exec deleteOldFiles 'c:\temp', 0, 'BAK', 1
Argument 2 (@days_to_age) is set to 0 to look at any age file. This should only delete the file you turned off the archiving bit for.
-- Update: January 2, 2009 - cleaned up a bit of the code near the end.
