SQL - How to delete all files older than X days of a specific extension in a specific directory

Scenario: I have a bunch of video .avi files in one folder that I want to delete automatically or when scheduled once they hit 30 days old.

I tried using SQL maitenance cleanup task but it didn't work. I guess this is only for .bak and .txt files. I've heard there are t-sql statements like xp_cmdshell that can do this but I need help writing the script.

Thanks!
LVL 1
dou2bleAsked:
Who is Participating?
 
brutaldevConnect With a Mentor Commented:
You can use a basic batch script which also works when calling it with xp_cmdshell:
forfiles /p "C:\VideoFiles" /s /m *.avi /d -30 /c "cmd /c del @file >NUL"

Open in new window

This will search for all .avi files in the direcrtory C:\VideoFiles that are older than 30 days and delete them. Run this in a Windows Task Schedule or with a DB task.
0
 
James MurrellProduct SpecialistCommented:

are these .avi file stored in db?

or do you just want them deleted from folder
0
 
dou2bleAuthor Commented:
From a folder. I've figured out how to delete all .avi files.

xp_cmdshell 'del g:\test\*.avi'

Now I just need to specify older than 30 days.

Thanks!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
James MurrellProduct SpecialistCommented:
While it is possible to do this via xp_cmdshell, I would not advise it. Create a vbscript or .bat file that will do the same thing, but you can scedule it via the windows sceduler.
0
 
James MurrellProduct SpecialistCommented:
batch file could be something like

forfiles -p "G:\test" -s -m *.avi -d -30 -c "cmd /c del @path"
0
 
James MurrellProduct SpecialistCommented:
sorry brutaldev i didn't refresh
0
 
Alpesh PatelAssistant ConsultantCommented:
Create SSIS package and in that check for folders for files. and check the created /modified date and based on that delete the 30 days old files.

Create SQL Job for Run the SSIS package and schedule it from SQL Job.
0
 
dou2bleAuthor Commented:
This is better than running a sql script. Thanks for the suggestion and clear answer.
0
 
dou2bleAuthor Commented:
Jut to clarify, the answers provided by both brutaldev and cs97jjm3 worked for me. But since brutaldev was first I accepted his solution. I didn't try the SSIS package since I liked the other option better. Thanks though, maybe it'll help someone else.
0
 
brutaldevCommented:
Thanks dou2ble.

Don't worry about the extra post cs97jjm3, happens to me all the time...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.