How to delete any backup files based on CURRENT day in a daily schedule.

Question

How to delete any backup files based on CURRENT day in a daily schedule.

hyeewang

Newbie

Points: 7
More actions
February 25, 2013 at 11:11 pm

#290272

How to delete any backup files based on CURRENT day in a daily schedule.
I create a sql script as follows. Its purpose is deleting any backup files created 2 days ago, based on current day, in a specified directory. The job runs everyday in a daily schedule.
However,my script does not work at all.
It only work at the very first day. After the first day,it does not work.
At the very first day, it delete files created 2 days ago.
At the second day, it delete files created 3 days ago.
At the third day, it delete files created 4 days ago.
.......
Actually, I want it to delete files which are created 2 days ago whatever day the script run at.
How can I fix it? Please help.
/*
Daily, delete any backup files that are 2 days ago based on current day in "daily" schedule.
*/
declare @filedir varchar(max)
set @filedir = 'c:\SQLBackups\Full\AdventureWorksDW';
declare @twodaysago varchar(50) = cast(DATEADD(day,-2,GETDATE()) as varchar)
print @twodaysago
declare @comm2 varchar(max)
set @comm2 = 'EXECUTE master.dbo.xp_delete_file 0,"' + @filedir + '",'+ '"bak"' +',"'+ @twodaysago + '"'
print @comm2
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
DECLARE @jobId BINARY(16)
use msdb;
-- add a job
EXEC dbo.sp_add_job @job_name = 'AdventureWorksDWdbbackuptest4tmp';
-- add job steps to job
EXEC sp_add_jobstep
@job_name = 'AdventureWorksDWdbbackuptest4tmp',
@step_name = 'AdventureWorksDW db backup test42tmp',
@subsystem = 'TSQL',
@command = @comm2,
@retry_attempts = 5,
@retry_interval = 5;
-- create a schedule for this job
EXEC sp_add_schedule
@schedule_name = 'AdventureWorksDWdbbackuptest43tmp',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20130224,
@active_end_date=99991231,
@active_start_time=120000,
@active_end_time=235959;
-- attach the schedule to the job
EXEC sp_attach_schedule
@job_name = 'AdventureWorksDWdbbackuptest4tmp',
@schedule_name = 'AdventureWorksDWdbbackuptest43tmp';
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @server_name = N'(local)',@job_name = 'AdventureWorksDWdbbackuptest4tmp';

Viewing 3 posts - 1 through 2 (of 2 total)

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

MMartin1 One Orange Chip Points: 27764 More actions · Answer 1

You may need to specify the extension of the file in the file name. Also it appears you are recreating the job everyday, If the job with that name already exists, then an exception will be raised.

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

homebrew01 SSC Guru Points: 55539 More actions · Answer 2

Is your purpose to not have any files more than 2 days old ?

You could try the Windows utility FORFILES. It has different syntax based on your Windows version. I use it to delete files older than 'X' days.

It only takes 1 line of code and you specify the path and file extension or *.*