Solved

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

Posted on 2011-09-26
10
542 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:dou2ble
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 36602123

are these .avi file stored in db?

or do you just want them deleted from folder
0
 
LVL 1

Author Comment

by:dou2ble
ID: 36602175
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
 
LVL 11

Accepted Solution

by:
brutaldev earned 350 total points
ID: 36602211
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
 
LVL 31

Expert Comment

by:James Murrell
ID: 36602212
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
 
LVL 31

Expert Comment

by:James Murrell
ID: 36602222
batch file could be something like

forfiles -p "G:\test" -s -m *.avi -d -30 -c "cmd /c del @path"
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:James Murrell
ID: 36602227
sorry brutaldev i didn't refresh
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36707657
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
 
LVL 1

Author Closing Comment

by:dou2ble
ID: 36712631
This is better than running a sql script. Thanks for the suggestion and clear answer.
0
 
LVL 1

Author Comment

by:dou2ble
ID: 36712661
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
 
LVL 11

Expert Comment

by:brutaldev
ID: 36712779
Thanks dou2ble.

Don't worry about the extra post cs97jjm3, happens to me all the time...
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now