Remove files older than 48 hours

Question

Post reply

Remove files older than 48 hours

Golova

Mr or Mrs. 500

Points: 578
More actions
January 28, 2008 at 12:42 pm

#178577

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! 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

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

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 1

sorry for stating the obviuos can you not simplydelete files older than two days?

Gethyn Elliswww.gethynellis.com

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

Here's a backup routine I wrote yrs ago. It deletes files. All you gotta do is switch it from deleting by GetDate() to DatePart... but this'll get you going in the right direction.

The line you're looking for is this:

Delete #BackupProcess where not DateAndTime < (GetDate() -2)

CREATE PROCEDURE [dbo].[spFullBackupAllUserDBs]

AS

/*

Created By: Sean McCown

Creation Date: 6/25/2003

Purpose: Full Backup of all user DBs on the system.

Walkthrough:

1. Get list of all user DBs on server.

2. Check that each DB has its own backup dir... if not, create it.

3. Delete old backup files. Defaults at 2 days, but can easily be changed.

4. Backup Databases.

Conventions:

@BasePath -- var that holds path to root backup dir.

@CurrDB -- var for holding current DB name in the cursor fetch list.

@folder -- var for full backup folder path for each DB.

#DirExist -- Temp table used to hold info returned from xp_fileexist. The DirExist col is really the only important

one. If it is '0' then the dir does not exist, and it will be created.

#BackupFiles -- Temp table for holding the list of files in the current DB folder. This is used to parse the date

and get the oldest files. Once obtained, the oldest files will be deleted from disk.

#BackupProcess -- Temp table that holds all files from #BackupFiles with the dates parsed. The actual deletes are

done from this table.

@back -- The full path and file name to the current DB backup file. This is used in the backup command itself.

Revision History:

*/

Declare @BasePath as varchar(1000)

Set @BasePath = 'D:\Backup\' + @@ServerName + '\' --Needs to end in '\'

-- ======================================================

-- BEGIN CURSOR 1 --Loops through user DBs in Master.

-- ======================================================

DECLARE UserDBs CURSOR

READ_ONLY

FOR SELECT Name from sysdatabases where Name NOT IN ('master', 'msdb', 'tempdb', 'model', 'Spotlight')

DECLARE @CurrDB varchar(1000)

OPEN UserDBs

FETCH NEXT FROM UserDBs INTO @CurrDB

WHILE (@@fetch_status <> -1)

BEGIN

/****************************************************

Check if dir exists for each DB. If not, create it.

*****************************************************/

declare @folder varchar(1000)

Create Table #DirExist

(

FileExist bit,

DirExist bit,

Other int

)

Set @folder = @BasePath + @CurrDB

Insert #DirExist

Exec xp_fileexist @folder

If (Select DirExist from #DirExist) = 0 -- 0 is returned by xp_fileexist when the dir doesnt exist.

BEGIN

set @folder = 'MD ' + @folder + '\'

Exec master..xp_cmdshell @folder

END

Drop Table #DirExist

/****************************************************

Check for old backup files, and delete if too old.

*****************************************************/

/************************************************

Get file list and place in temp table.

*************************************************/

create table #BackupFiles

(

Ident int identity (1,1) not null,

col1 varchar(1000) null,

)

set @folder = 'dir /B ' + @BasePath + @CurrDB + '\'

insert into #BackupFiles

Exec master..xp_cmdshell @folder

--************************************************

/*************************************************

Parse date and time out of filenames and place into another temp table that holds both

the filename, and the date, ordered by datetime. this puts the files in order from earliest

to latest to make the cursor easier for the restore.

**************************************************/

-- right(col1, 3) AS Ext,

-- substring(right(col1, 6), 1, 2) AS Secs,

-- substring(right(col1, 8), 1, 2) AS Mins,

-- substring(right(col1, 10), 1, 2) AS Hrs,

-- substring(right(col1, 12), 1, 2) AS Days,

-- substring(right(col1, 14), 1, 2) AS Months,

-- substring(right(col1, 18), 1, 4) AS Years,

select col1 AS FileName,

Convert(DateTime,

substring(right(col1, 14), 1, 2) + '/' + --Month

substring(right(col1, 12), 1, 2) + '/' + --Day

substring(right(col1, 18), 1, 4) + ' ' + --Year

substring(right(col1, 10), 1, 2) + ':' + --Hrs

substring(right(col1, 8), 1, 2) + ':' + --Mins

substring(right(col1, 6), 1, 2) --Secs

) AS DateAndTime

into #BackupProcess from #backupfiles

where col1 is NOT NULL

Order by DateAndTime asc

Delete #BackupProcess where not DateAndTime < (GetDate() -2)

Declare @DelFile varchar(1000)

DECLARE OldFiles CURSOR

READ_ONLY

FOR SELECT FileName from #BackupProcess

DECLARE @CurrFileName varchar(1000)

OPEN OldFiles

FETCH NEXT FROM OldFiles INTO @CurrFileName

WHILE (@@fetch_status <> -1)

BEGIN

Set @DelFile = 'Del ' + @BasePath + @CurrDB + '\' + @CurrFileName

Exec master..xp_cmdshell @DelFile

FETCH NEXT FROM OldFiles INTO @CurrFileName

END

CLOSE OldFiles

DEALLOCATE OldFiles

Drop Table #BackupProcess

-- ==============================================

-- ==========BACKUP CURRENT DATABASE=============

-- ==============================================

declare @back varchar(1000),

@BackupCmd nvarchar(2000),

@SQ char(1)

SET @SQ = char(39)

SET @back = @BasePath + @CurrDB + '\Full' + @CurrDB + Convert(varchar(8), GetDate(), 112) +

Replace(Convert(varchar(8), GetDate(), 108), ':', '') + '.BAK'

SET @BackupCmd = 'xp_backup_database @database = ' + @SQ + @CurrDB + @SQ + ', @filename = ' + @SQ + @back + @SQ

EXEC (@BackupCmd)

--backup database @CurrDB

--to disk = @back

Drop Table #BackupFiles

FETCH NEXT FROM UserDBs INTO @CurrDB

END

CLOSE UserDBs

DEALLOCATE UserDBs

GO

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 3

Do you need to delete that from the source server or the destination server.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Chand00 SSCertifiable Points: 6466 More actions · Answer 4

Please use the following script. You have to modify in someparts like when the date is 06 it returns only 6 you have to add 0. Test the script in a test server.

DECLARE @curDateTime datetime

DECLARE @prevDateTime datetime

DECLARE @strDateTime varchar(200)

DELCARE @strDelCmd varchar(500)

SET @curDateTime=getdate()

SET @prevDateTime=dateadd(day,-2,getdate())

SET @strDateTime=''

SET @strDateTime=@strDateTime+convert(varchar,datepart(year,dateadd(day,-2,getdate())))

SET @strDateTime=@strDateTime+convert(varchar,datepart(month,dateadd(day,-2,getdate())))

SET @strDateTime=@strDateTime+convert(varchar,datepart(day,dateadd(day,-2,getdate())))

SET @strDateTime=@strDateTime+convert(varchar,datepart(hh,dateadd(day,-2,getdate())))

--SET @strDateTime=@strDateTime+convert(varchar,datepart(mi,@prevDateTime))

SELECT @strDateTime

SET @strDelCmd='del /q *filename_'+@strDateTime+'*'

EXECUTE xp_cmdshell @strDelCmd,no_output

Regards

Mohan

[font="Verdana"]Thanks
Chandra Mohan[/font]