SQL Script to Delete Files Based on Modified Date

OverlookTech
OverlookTech used Ask the Experts™
on
How would I create a stored procedure which will delete all .jpg files from c:\test which have a modified date less than getdate() - 30?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You can use xp_cmdshell to execute DOS commands. Something like:
exec xp_cmdshell 'del c:\test\example.jpg'
Here is an article on the same: http://www.kodyaz.com/articles/delete-file-from-sql-server-xp-cmdshell-ole-automation-procedures.aspx
P.

Commented:
Also, you can use the FORFILES dos comand to specify the modified data:
Here is a pdf explanin how you can use it: http://www.dialogmedical.com/kb/000026/DeletingFilesByAge.pdf
P.

Author

Commented:
Right.  I'm using the following code to configure sp_cmdshell for use but how would I do a mass delete of multiple files that are older than let's say (today - 3 days)?

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Commented:
What is your operating system? Seems like the FORFILES command is not available in Windows XP.
P.

Author

Commented:
MS Server 2003 R2

Author

Commented:
OK, so I'm using this:

DECLARE @Variable int
SET @Variable = DATEDIFF(DD, GETDATE()-3, GETDATE())
xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -30'

How do I replace the 30 with @Variable without getting an error

Commented:
How about this:

DECLARE @Variable int
SET @Variable = DATEDIFF(DD, GETDATE()-3, GETDATE())
xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -'+cast(@Variable as Varchar(5))

Open in new window

Author

Commented:
I get
Incorrect syntax near 'xp_cmdshell'.

Commented:
So if you run it without the @variable, it runs fine. Right?
Also, try it this way:

DECLARE @Variable int,
        @DOSCmd  varchar(500)
 
SET @Variable = DATEDIFF(DD, GETDATE()-3, GETDATE())
 
SET @DOSCmd = 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -'+cast(@Variable as Varchar(5))
 
-- Printing to see if the value in the string are concatinating correctly.
Print @DOSCmd
 
-- Execute the command
xp_cmdshell @DOSCmd

Open in new window

Author

Commented:
This is the only thing that I can get to work.  It accomplishes the task of deleting days -30 through -40 but I was hoping to set a variable and loop through the query rather than runing 10 queries back to back.

---Turn on xp_cmdShell---

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE  
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0

---Delete Old Files---

exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -30', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -31', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -32', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -33', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -34', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -35', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -36', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -37', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -38', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -39', NO_OUTPUT
exec xp_cmdshell 'FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -40', NO_OUTPUT

---Disable xp_cmdShell---

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE  
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0

Author

Commented:
I found my own solution:

---Turn on xp_cmdShell---

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE  
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0

---Delete Old Files---

DECLARE @FileLocation Varchar(250)
DECLARE @SQL_Command VarChar(200)
DECLARE @SQL_Command1 VarChar(200)
DECLARE @Variable varchar(25)

SET @Variable = 30
SET @FileLocation = (SELECT BarcodeImageStorageLocation FROM tblConfiguration)

WHILE @Variable <=35
BEGIN

SET @SQL_Command = 'FORFILES /p ' +  @FileLocation +  ' /m *.* /s /c "CMD /C del @FILE" /d -' + @Variable
SET @SQL_Command1 = 'xp_cmdshell ''' +@SQL_Command+''''
PRINT @SQL_Command ---  FORFILES /p c:\Test /m *.* /s /c "CMD /C del @FILE" /d -0  ---

EXEC (@SQL_Command1)
SET @Variable = @Variable + 1
END


---Disable xp_cmdShell---

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE  
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0

Commented:
Awesome. Sorry I couldn't test because my OS is Windows XP and the FORFILES command is not included.
I am glad it worked out.
Cheers!!
P.
Commented:
Overlloktech,
I am sorry to ask but I think I am entitled in reciving partial points here.... wouldn't you say?
P.

Author

Commented:
Somewhat helpful but I was looking for a way to loop through the variable (which I found myself).  Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial