Wednesday, April 8, 2015

Deleting Old backup files from the file system by using SQL script

Some times you may frustrate to use maintenance plans. Some times complete work by using T-SQL scripts is better(I usually go for the scripts) than maintenance plans.
If you want to delete the backup files from the filesystem, You can do by using T-SQL script.
Please follow the below script and pass the parameters accordingly your server/system.

DECLARE @DeletedDate DATETIME
            SET @DeletedDate = DateAdd(day, -15, GetDate())  -- Its your setting the number of days
            EXECUTE master.sys.xp_delete_file
            0, -- FileTypeSelected (0 = FileBackup, 1 = FileReport)
            N'E:\FullBackup\', -- folder path (trailing slash)
            N'bak', -- file extension which needs to be deleted (no dot)
            @DeletedDate, -- date prior which to delete
            1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

No comments:

Post a Comment