Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Delete files olde than 48 hours based on filename, not date modified.


Delete files olde than 48 hours based on filename, not date modified.

Author
Message
Golova
Golova
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 157
Hi,

Every hour i have files that get copied from one location to another and renamed with adding a date and time to the file name (FileName_200801281130.txt).

I need to be able to delete files that are older then 48 hours. i looked at the previous posts and there are only refer to deleting the files older then x number of day, but i need hours...

Please help! Smile
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14383 Visits: 9729
If you're doing this in T-SQL, the only way I know of to do this is use xp_CmdShell to insert a list of the file names into a temp table, parse out the date and time from the file name, then create a dynamic SQL command using xp_CmdShell to delete each file.

Since xp_CmdShell is a security hole all by itself, I can't recommend using that method. I think you'd be better off using a language that's designed to work with the file system directly, as opposed to T-SQL. (This post is in the T-SQL forum.) I haven't programmed in VB for a while, but I'm pretty sure it could do all that pretty efficiently.

Even an SSIS package would be safer than xp_CmdShell.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
I agree with GSquared. 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.



My blog: http://jahaines.blogspot.com
Golova
Golova
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 157
Hi guys,

I came up with this code. and it worksSmile

declare @d as varchar (8)
declare @d1 as varchar (12)
DECLARE @NewFileName VarChar(30)
DECLARE @SQL_Command VarChar(200)
DECLARE @SQL_Command1 VarChar(200)
DECLARE @SQL_Command2 VarChar(200)
DECLARE @SQL_Command3 VarChar(200)
DECLARE @BACKUP_DATE as VARCHAR(64)
DECLARE @BACKUP_DATE1 as VARCHAR(64)
DECLARE @BACKUP_DATE2 as VARCHAR(64)

SET @BACKUP_DATE = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())
as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate())
as varchar(4))))+cast(day(getdate()) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)
-- + SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

--print @BACKUP_DATE

SET @BACKUP_DATE1 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())
as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-3)
as varchar(4))))+cast(day(getdate()-3) as varchar(4))
--+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)
--+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

SET @BACKUP_DATE2 = cast(year(getdate()) as varchar(4))+substring('0',1,2-len(cast(month(getdate())
as varchar(4))))+cast(month(getdate()) as varchar(4))+substring('0',1,2-len(cast(day(getdate()-2)
as varchar(4))))+cast(day(getdate()-2) as varchar(4))+ SUBSTRING(CONVERT(Char,GetDate(),120),12,2)
--+ SUBSTRING(CONVERT(Char,GetDate(),120),15,2)

--print @BACKUP_DATE1
--xp_cmdshell 'FORFILES /p w:\Backups\ESSBASE /m *.* /s /c "CMD /C del @FILE" /d -2'

SET @SQL_Command = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE1 + '*.bak /s /c "CMD /C del @FILE"'
SET @SQL_Command1 = 'xp_cmdshell ''' +@SQL_Command+''''
print @SQL_Command1

EXEC (@SQL_Command1)

SET @SQL_Command2 = 'FORFILES /p w:\Backups\ESSBASE /m ESSBASE_' + @BACKUP_DATE2 + '*.bak /s /c "CMD /C del @FILE"'
--print @SQL_Command
SET @SQL_Command3 = 'xp_cmdshell ''' +@SQL_Command2+''''
print @SQL_Command3
EXEC (@SQL_Command3)

-----------------

I have anothe issue now. I have tried to do the same with xp_delete_file and i can not make it work. I will probably add a separate post for this issueSmile
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7643 Visits: 18084
Well - now that you have SQL server doing your file maintenance - any chance you can figure out how it can turn the coffee on in the morning? BigGrinTongueWinkw00tCoolHehe

Now THERE would be a feature....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
dbajunior
dbajunior
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 1603
Since you handled the maintenance part, here's a little SSIS app that actually watches folders for changes and returns file names. It allows you to set a wait period as well. Not sure if it will help you, but you can check it out at: http://www.sqlis.com/23.aspx
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45191 Visits: 39925
Matt Miller (1/30/2008)
Well - now that you have SQL server doing your file maintenance - any chance you can figure out how it can turn the coffee on in the morning? BigGrinTongueWinkw00tCoolHehe

Now THERE would be a feature....


Heh... POKE the game port to fire an opto-coupled TRIAC...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
stricknyn
stricknyn
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 678
I agree with the posts that say you should use a programming language to do this. But if you don't have programming software and have to use SQL Server 05 and you have SSIS at your disposal (ships with SQL 05), then I'd use this tool to do this. It's meant for it and probably only take 3 or 4 SSIS tasks to do what you need to do.

You can also deploy your SSIS package to a server and schedule a job to run this every hour as well.

Thanks,

Strick



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search