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

How to delete any backup files based on CURRENT day in a daily schedule. Expand / Collapse
Author
Message
Posted Monday, February 25, 2013 11:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, May 23, 2013 11:55 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:42 PM
Points: 459, Visits: 1,065
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
Posted Saturday, May 25, 2013 8:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
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 *.*



Post #1456790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse