Delete Files with a SQL Job in SQL Server

Introduction

In this article, we will learn about Delete Files with a SQL Job in SQL Server. Find more about SQL Server here- SQL Server

SQL Query for deleting the files

Suppose you want to delete some files from a directory. This could be one of the easiest tasks for anyone who is familiar with Windows. But what if we want to check the files periodically in some directory and if any file is found, we delete them? To do this we have some options.

• By a windows service that keeps on checking and deleting files a regular intervals.
• By a SQL Job (Taking advantage of a SQL Server Agent Service)

Here I am going to discuss deleting files using a SQL Job. In SQL Job, you need to use a SQL query in your SQL Job.

In the SQL query to delete the files, you need to call a predefined stored procedure "xp_cmdshell". But before calling "xp_cmdshell", we also need to enable the Command Shell of your SQL Server if it is not enabled in your system.

Here is the full SQL query that is used for deleting the files.

Note 

The topic "How to create a SQL Job in SQL Server" is discussed in Part 2 of this article.

   How to enable the COMMAND SHELL using SQL statement?

--Script to enable the XP_CMDSHELL
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options, 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

How to delete files using COMMAND SHELL  SQL statement.

-- Suppose we need to delete all .txt files under "D:\temp\Testing\"
xp_cmdshell 'DEL D:\temp\Testing\*.txt'

How to Create SQL Job in SQL Server Agent?

Go to SQL Server Agent and start the Server Agent service.

Right-click the "Jobs" under "SQL Server Agent" and select "New Job"

On clicking "New Job", the following window opens. In the left pane, you see the following options.

• General
• Steps
• Schedule
• Alerts
• Notification
• Targets

On the "General" page, you need to give the name of your job as "FileDeleter" and its description.

Now go to the "Steps" page and click the "New" step.

The following window appears. In this step-defying window, you need to give your step name (e.g. File Delete). In the command box, paste your SQL query for the file deletion. Since in this job, we have only one step, so we need to set this step as the final step. This you could do by going to the "Advanced" page under the "General" page in the same window.

On clicking the "Advanced" page, you will see actions for success and failure. Set "on success action" to "Quit the job reporting success" and click OK.

Now we set the schedule of our job. Click the "Schedule" page and create a new schedule by clicking "New...". The following window appears. In this window, give the name of the job schedule and set the occurring frequency as "Daily". Then we set the timing (e.g. 12:00 AM midnight) and duration for how long this SQL job should be working (e.g. Aug 8, 2012 to Nov 8, 2012). After setting these parameters, click OK to finish your SQL Job Creation.

After job creation, just right-click the newly created job "FileDeleter" and start the job.

Conclusion

In this article, we learned about how to delete files with a SQL Job, how to enable the COMMAND SHELL using SQL statements, and how to Create SQL Job in SQL Server Agent in SQL Server.