Check Date Stamp and Delete Old File --Question

Question

Check Date Stamp and Delete Old File --Question

sqlquest2575

SSCommitted

Points: 1802
More actions
June 18, 2011 at 6:52 am

#240027

Hello Friends...I would really appreciate if you could help me in solving a sql problem....
I've 4 text files and all of them have date stamp in them and i want to keep 3 recent files and delete the oldest file. I've a script to delete all files from the folder but not sure how add retention in the Script
File Sample :-
TeraOutput2011-05-01T103109.567.txt
TeraOutput2011-05-07T103109.767.txt
TeraOutput2011-05-14T103109.098.txt
TeraOutput2011-05-21T103109.654.txt
My Script
use Master
go
Declare @sql varchar(250)
set @sql='Exec master.dbo.xp_cmdshell ''Del /Q f:\test\'''
Exec (@sql)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

sqlquest2575 (6/18/2011)
Hello Friends...I would really appreciate if you could help me in solving a sql problem....
I've 4 text files and all of them have date stamp in them and i want to keep 3 recent files and delete the oldest file. I've a script to delete all files from the folder but not sure how add retention in the Script
File Sample :-
TeraOutput2011-05-01T103109.567.txt
TeraOutput2011-05-07T103109.767.txt
TeraOutput2011-05-14T103109.098.txt
TeraOutput2011-05-21T103109.654.txt
My Script
use Master
go
Declare @sql varchar(250)
set @sql='Exec master.dbo.xp_cmdshell ''Del /Q f:\test\'''
Exec (@sql)

I would read the file names from the target directory, write them into a temporary table then add whatever logic is needed to select from that table the files to be deleted then issue delete statement/s.

Here is an easy to follow example of how to read file names from a given folder... http://software.allfaq.org/forums/p/151817/302366.aspx

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

sqlquest2575 SSCommitted Points: 1802 More actions · Answer 2

Thank you so much Paul for your quick reply.... I've just started learning tsql and I am not sure how to add my logic in the script..could you please help me here.