June 18, 2011 at 1:54 pm
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. I am new to Tsql and need help
File Sample :-
TeraOutput2011-05-01T103109.567.txt
TeraOutput2011-05-07T103109.767.txt
TeraOutput2011-05-14T103109.098.txt
TeraOutput2011-05-21T103109.654.txt
The files will come in the folder by a sql job every week and I need to check the datestamp and delete them through tsql---Please Help
My Script
use Master
go
Declare @sql varchar(250)
set @sql='Exec master.dbo.xp_cmdshell ''Del /Q f:\test\'''
Exec (@sql)
June 18, 2011 at 2:37 pm
I would recommend you use PowerShell for sys admin scripting tasks. You can schedule your scripts to run periodically using the SQL Agent PowerShell step type (2008+) or invoking PowerShell from a CmdExec step type (2005+). Here is a one line PowerShell to delete files older than 14 days:
dir E:\files\TeraOutput*.txt |? {$_.LastWriteTime -lt (get-date).AddDays(-14)} | del
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply