Tuesday, November 16, 2010

Delete files based on Last Modified Date using FORFILES.exe

Deleting files based on last modified date or running some command on these files has been a real sticking point for Windows users.  Unix/Linux people would always hold some of those cool command line features over our heads to prove why Linux is better than Windows.

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

I don't know why this isn't in Windows XP.  This is a great tool