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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » How to delete any backup files based on...

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

Rate Topic

Display Mode

Topic Options

Author

Message

hyeewang

Posted Monday, February 25, 2013 11:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 11:39 PM
Points: 1, Visits: 3

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';

Post #1423886

mmartin1

Posted Yesterday @ 11:55 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:53 AM
Points: 194, Visits: 643

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.

Post #1456139

« Prev Topic | Next Topic »

Permissions