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
556 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

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.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

679 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