Hi, I am taking log backups every hour, differential backup every night and full backup every week on Saturday. The full backup some times it takes 5 hrs to complete and sometimes 6 hrs so the file is created either on Saturday night or next day Sunday around 12:30 AM. I have scheduled a step which will delete old backups keeping the 7 days backups. So the script i am using delete file based on days. So as i am not sure when the backup job completes i.e. on Saturday or Sunday, I am unable to configure how many days I need to mention to delete backups. Below is the script i am using to delete old files. CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles] @path nvarchar(256), @extention nvarchar(10), @days int AS BEGIN SET NOCOUNT ON; DECLARE @DeleteDate nvarchar(50) DECLARE @DeleteDateTime datetime SET @DeleteDateTime = DateAdd(DAY, -@days, GetDate()) SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-')) EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1 END GO exec usp_DeleteOldBackupFiles '\\Path_to_folder', 'dif', 7 go exec usp_DeleteOldBackupFiles '\\Path_to_folder', 'bak', 7 go exec usp_DeleteOldBackupFiles '\\Path_to_folder', 'trn', 7 go
If you add the cleanup as Another jobstep in the backup-job, you'll know when the backup was created (as it will be a second or so Before running the cleanup job step) That way you can also configure the job to fail if the backup jobstep fails, so you don't end up deleting old backupfiles when there are no new backups taken. You could also use the information in msdb.dbo.backupset and msdb.dbo.backupfile to both find out when a backup was finished.