Deleting old backup files from the file system using T-SQL

Some times it is very useful to have a script to delete the old files which are increasing the space keeping in the drive.. We always delete them manually..We may forget some times and resulting automatic backups may fail... So have this script and automate along with the backup script to solve space issues..

DECLARE @DelDate DATETIME
SET @DelDate = DateAdd(day, -14, GetDate()) -- The date where you want to delete files upto...

EXECUTE master.sys.xp_delete_file
1,        -- File Type Selected  (0 = FileBackup, 1 = FileReport)
N'E\Backups\', -- folder path where you are deleting the files from ending with slash
N'bak', -- file extension which needs to be deleted, dont required the dot(.)
@DelDate,
1 -- subfolder flag (1 = include files in first subfolder level, 0 = not)

Create a folder in file system using T-SQL

--I) How to create a folder in our file system using T-SQL

DECLARE @SQL VARCHAR(500)
DECLARE @DestPath VARCHAR(200)



SET    @DestPath = 'E\SQLFolder\'
SET    @SQL =    'MKDIR ' + @DestPath +'YourFolderName'

EXEC    xp_cmdshell    @SQL, no_output
PRINT    @SQL
EXEC (@SQL)

Note: Please make sure 'XPCmdSheeEnabled' True.