Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Delete files olde than 48 hours based on filename, not date modified. Expand / Collapse
Author
Message
Posted Monday, January 28, 2008 1:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 1, 2013 6:32 PM
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! :)
Post #448540
Posted Monday, January 28, 2008 2:58 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #448603
Posted Monday, January 28, 2008 3:52 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 1:41 PM
Points: 2,278, Visits: 3,058
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
Post #448627
Posted Wednesday, January 30, 2008 10:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 1, 2013 6:32 PM
Points: 20, Visits: 157
Hi guys,

I came up with this code. and it works:)

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 issue:)
Post #449536
Posted Wednesday, January 30, 2008 2:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,158, Visits: 15,269
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? :D:P;)

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?
Post #449704
Posted Thursday, January 31, 2008 8:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:22 AM
Points: 123, Visits: 1,474
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



Post #450009
Posted Thursday, January 31, 2008 11:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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? :D:P;)

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #450323
Posted Friday, February 1, 2008 11:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, February 9, 2014 10:16 PM
Points: 265, Visits: 587
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



Post #450666
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse