question

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