As a SQL Server DBA, I like to code in T-SQL mostly. Recently I had a requirement where I need to delete files on conditional basis. I could have done the same writing a simple batch script or vb script or powershell but as I said conditional, there was small business logic, where I had to validate some conditions against few tables and then delete files. I wrote the same in T-SQL.
Let’s take a simple scenario; delete all text files from a folder older than 2 days.
Method 01: (Using maintenance plan)
We can create a maintenance plan, add “Maintenance cleanup Task” and then configure the folder path. I am not going to explain this method here because, our main goal is to delete files using TSQL code.
Method 02: (Using xp_delete_file)
Note: Maintenance plan runs the same extended stored proc xp_delete_file behind the scene to delete files.
DECLARE @DeletedDate SMALLDATETIME = GETDATE() - 2 --Two days older files , @Folder NVARCHAR(100) = N'E:\SQLINDIA\Test' --Path , @FileExtension NVARCHAR(10) = N'txt' --File extension DECLARE @xml XML , @ConvertedDateFormat NVARCHAR(50) SET @xml = (SELECT @DeletedDate as DeleteDate FOR XML PATH('')) SELECT @ConvertedDateFormat = t.c.value('.', 'varchar(100)') FROM @xml.nodes('/DeleteDate') t(c) EXECUTE master.dbo.xp_delete_file 0,@Folder,@FileExtension,@ConvertedDateFormat
Method 03: (Using xp_cmdshell)
EXEC sp_configure N'show advanced options', 1 GO RECONFIGURE EXEC sp_configure N'xp_cmdshell', 1 GO RECONFIGURE DECLARE @DeletedDays VARCHAR(10) = '2' --Older than days , @Folder VARCHAR(100) = N'E:\SQLINDIA\Test' --Folder path , @FileExtension VARCHAR(10) = N'txt' --File extension DECLARE @cmd VARCHAR(1000) SET @cmd = 'forfiles -p "'+@Folder+'" -s -m *.'+@FileExtension+'* /D -'+@DeletedDays+' /C "cmd /c del @path"' EXEC xp_cmdshell @cmd EXEC sp_configure N'xp_cmdshell', 0 GO RECONFIGURE EXEC sp_configure N'show advanced options', 0 GO RECONFIGURE
Method 04: (Using OLE automation)
I find this method more robust because of OLE automation; we can delete files not only on basis of file created date but also on basis of file modified and accessed date.
EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @DeletedDate SMALLDATETIME = GETDATE() -2 --Older than days , @Folder VARCHAR(100) = N'E:\SQLINDIA\Test' --Folder path , @FileExtension VARCHAR(10) = N'txt' --File extension DECLARE @tFiles TABLE(ID INT, Files VARCHAR(1000), d int, f int, ext VARCHAR(10)) DECLARE @cnt INT = 0, @path VARCHAR(1000), @dt SMALLDATETIME INSERT INTO @tFiles(Files, d, f) EXEC xp_dirtree @Folder, 0, 1 --Getting files from folder UPDATE @tFiles SET ext = REVERSE(SUBSTRING(REVERSE(Files),1, CHARINDEX('.', REVERSE(Files))-1)) , Files = @Folder + '\' + Files; DELETE FROM @tFiles WHERE ext <> @FileExtension --Deleting that are not given extension UPDATE @tFiles SET ID = @cnt , @cnt = @cnt + 1 --creating sequence for while loop SELECT * FROM @tFiles SELECT @cnt = COUNT(1) FROM @tFiles WHILE @cnt >= 1 BEGIN SELECT @path = Files FROM @tFiles WHERE ID = @cnt IF @path IS NOT NULL BEGIN SELECT @dt = CreatedDate FROM [dbo].[ufn_fileOperation](@path, 1) /* Know more about function ufn_fileOperation