question

sakethg.sql avatar image
sakethg.sql asked

How to delete old backup files using T-SQL

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
backupsdelete-backup
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jason_clark03 avatar image
jason_clark03 answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I think what @Magnus is saying is that if you know the job is started on a Saturday, then in the delete step instead of simply taking 7 days from getdate(), why not test what day it is and if Saturday, use 7, and if Sunday use 8
1 Like 1 ·
Hi Magnus, Thanks for replying. Actually i am executing the stored procedure as a next step after backup job and if backup step fail then the clean up step will not run. The problem with the stored proc is that i have to manually change the day from 7 to 8 or 6 if the backup is created on Saturday or Sunday. So i am looking for a script to clean up files without manually changing the day from 7 to other number.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.