Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Deleting old files (with/without archive bit)

By Gaby Abed,

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.

Total article views: 1906 | Views in the last 30 days: 9
Related Articles

Urgent:VB Script to delete 2-days old backup automatically

Automation Script to delete older backup in Network drive


Deleting Older backups

Deleting Older backups


SQL Backups Compression Archive

This script takes the last full backup includes diff and logs backups until the next full backup in ...


Script for Creating backup sp before modifying orginal sp

Script for Creating backup sp before modifying orginal sp

archiving data    
backup / restore    

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones