How to delete files using sql query from SQL Server

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.

I have used a function called ufn_fileOperation, I would recommend you to visit an article here if you are new to OLE automation in SQL Server.


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
File system operations in SQL Server using OLE Automation
*/ IF @dt <= @DeletedDate BEGIN SELECT [FileName],[Operation] FROM [dbo].[ufn_fileOperation](@path, 3) END END SELECT @path = NULL, @dt = GETDATE() SET @cnt -= 1 END EXEC sp_configure 'Ole Automation Procedures', 0; GO RECONFIGURE; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO

One thought on “How to delete files using sql query from SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve : *
1 × 21 =