Windows Server 2000/2003/2008 has a command called ForFiles.exe (http://technet.microsoft.com/en-us/library/cc753551(WS.10).aspx). This is a great tool once you get used to the sytnax. You can find files based on Last Modified Date, then run a command(like del) on each of those files.
FORFILES /P E:\MSSQL\Backup /S /M *.full.zip /D -30 /C “cmd /c del @path”
/S – look at subdirectories
*.full.zip – that is the file pattern, you could have it be like *.txt for example
/D -30 = files greater than 30 days
/C – thats the command you want to run
Here is what I use in my SQL script:
1: declare @full_location varchar(8000)
2: SET @full_location = @backup_location + @backup_db_name
3: SET @command = 'forfiles -p "' + @full_location + '" -s -m *.full.zip -d -1 -c "cmd /c move /y @path "' + @archive_destination
4: print @command
5: EXEC xp_cmdshell @command, NO_OUTPUT
6: --print @command
7:
I don't know why this isn't in Windows XP. This is a great tool