Deleting old files (with/without archive bit)
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.
use msdb
if exists(select name from sysobjects where name = 'deleteOldFiles')
drop proc deleteOldFiles
go
-- Simple procedure to delete any type of file in the system. It expects all parameters to be passed
-- when called except for extension, which defaults to all files. You can remove the default value and
-- have all four arguments mandatory.
--
-- @basedir => <DRIVE LETTER>:\<SOME PATH>\<ANOTHER PATH>\etc.
-- I can't guarantee that if you use a \\UNC\PATH that this will work. It may be better to map a
-- drive letter to the path first.
--
-- @days_to_age => Delete anything older than OR equal to this number of days.
--
-- @extnesion => format of BAK, TRAN, DOCX etc. Do not include the '.' before it
--
-- @checkarchives => Do not delete this file if the archive bit has not been cleared
-- For example, if you backup your databases to a directory, those backups have the archive bit
-- set. Let's say you have a third party program take these backups and store them on to a tape
-- for long term needs, it would normally (hopefully) clear the archive bit. Checking that this
-- has been cleared will add a layer of protection and prevent you from deleting any backups
-- that have not been backed up to tape offsite.
--
create procedure deleteOldFiles
@basedir sysname,
@days_to_age int,
@extension varchar(10) = '*',
@checkarchives int = 0
as
set nocount on
declare @ctr int,
@entryname varchar(256),
@numrows int
create table #raw_dir
(
raw_data varchar(512)
)
create table #dos_dir
(
entrydate smalldatetime,
entryname varchar(256)
)
declare @command varchar(256)
if (right(@basedir, 1) <> '\')
set @basedir = @basedir + '\'
if (left(@extension, 1) = '.')
set @extension = replace(@extension, '.', '')
select @command = 'dir "' + @basedir + '*.' + @extension + '"'
insert into #raw_dir(raw_data)
exec master.dbo.xp_cmdshell @command
delete from #raw_dir
where (raw_data like ' %'
or raw_data like '%<DIR>%'
or raw_data is NULL
or raw_data like 'File Not Found')
-- if you get date conversion errors when you test this, do a select * from #raw_dir before this
-- section to see any exceptions that are popping up and add them to the delete statement above
insert #dos_dir(entrydate, entryname)
select cast((substring(raw_data, 7, 4) + '-' + -- puts year in front
substring(raw_data, 4, 2) + '-' + -- in my case, month comes after day in a dos output
substring(raw_data, 1, 2)) as smalldatetime), -- but feel free to flip this line with the previous
substring(raw_data, 40, 512) from #raw_dir
if (@checkarchives = 0)
begin
delete from #dos_dir where entrydate > (getdate() - @days_to_age)
while (select count(*) from #dos_dir) > 0
begin
select top 1 @entryname = entryname from #dos_dir
select @command = 'del "' + @basedir + @entryname + '"'
delete from #dos_dir where entryname = @entryname
print 'deleting ' + @basedir + @entryname + ' ...'
exec master.dbo.xp_cmdshell @command, no_output
end
end
else -- this point means we want to take into account the Archive bit.
begin
create table #raw_archive
(
raw_data varchar(512)
)
create table #dos_archive
(
archive char(1),
entryname varchar(256)
)
select @command = 'attrib "' + @basedir + '*.' + @extension + '"'
insert into #raw_archive(raw_data)
exec master.dbo.xp_cmdshell @command
delete from #raw_archive
where (raw_data like ' %'
or raw_data like '%<DIR>%'
or raw_data is NULL
or raw_data like 'File Not Found')
insert into #dos_archive
select substring(raw_data, 1, 1), substring(raw_data, 12, 256)
from #raw_archive
update #dos_archive
set entryname = replace(entryname, @basedir, '')
delete from #dos_dir where entrydate > (getdate() - @days_to_age)
or entryname in (select entryname from #dos_archive where archive = 'A')
while (select count(*) from #dos_dir) > 0
begin
select top 1 @entryname = entryname from #dos_dir
select @command = 'del "' + @basedir + @entryname + '"'
delete from #dos_dir where entryname = @entryname
print 'deleting ' + @basedir + @entryname + ' ...'
exec master.dbo.xp_cmdshell @command, no_output
end
drop table #raw_archive
drop table #dos_archive
end
drop table #dos_dir
drop table #raw_dir
go
-- example execution:
-- exec deleteOldFiles @basedir = 'c:\temp', @days_to_age = 5, @extension = 'bak', @checkarchives = 1
