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.

Method 03: (Using xp_cmdshell)

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.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: