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
578 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 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