Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
603 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1400 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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