InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 16:03:01 GMT20http://www.sqlservercentral.com/Forums/Topic1423886-146-1.aspxYou 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.Thu, 23 May 2013 11:55:49 GMTmmartin1http://www.sqlservercentral.com/Forums/Topic1423886-146-1.aspxHow 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 @twodaysagodeclare @comm2 varchar(max)set @comm2 = 'EXECUTE master.dbo.xp_delete_file 0,"' + @filedir + '",'+ '"bak"' +',"'+ @twodaysago + '"'print @comm2DECLARE @ReturnCode INTSELECT @ReturnCode = 0DECLARE @jobId BINARY(16)use msdb;-- add a jobEXEC 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 jobEXEC 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 jobEXEC sp_attach_schedule@job_name = 'AdventureWorksDWdbbackuptest4tmp',@schedule_name = 'AdventureWorksDWdbbackuptest43tmp';EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @server_name = N'(local)',@job_name = 'AdventureWorksDWdbbackuptest4tmp';Mon, 25 Feb 2013 23:11:05 GMThyeewang