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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Delete files olde than 48 hours based on...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Golova

Posted Monday, January 28, 2008 1:07 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 01, 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

GSquared

Posted Monday, January 28, 2008 2:58 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Adam Haines

Posted Monday, January 28, 2008 3:52 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:39 PM
Points: 2,278, Visits: 2,998

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

Golova

Posted Wednesday, January 30, 2008 10:17 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, February 01, 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

Matt Miller (#4)

Posted Wednesday, January 30, 2008 2:35 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 6,998, Visits: 13,947

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;) w00t

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

dbajunior

Posted Thursday, January 31, 2008 8:58 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 8:43 PM
Points: 122, Visits: 978

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

Jeff Moden

Posted Thursday, January 31, 2008 11:16 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:57 PM
Points: 32,906, Visits: 26,790

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;) w00t

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #450323

stricknyn

Posted Friday, February 01, 2008 11:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 12:32 PM
Points: 264, Visits: 577

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 »

Permissions