SQL Server Job To Automatically Delete files

In some scenario if we are taking a database backup or creating some log files from the ETL package or some other files from the system, then we need to delete the files from the system depending on some number of days.

So in this article we will delete the “n” number of days old file from SQL Server Job.

To do this we will create one job and some credentials to delete the files.

Step 1

Create a new credential that will delete the file and execute the command.

Expand the SQL Server node then select Security --> Credentials.

Enter the credentials details and click on OK.



Once the credentials are created the next step is create a proxy to run the command job.

Step 2

To create a proxy, expand the SQL Server Agent then expand the proxies.

Select “Power shell” and click on new.



Enter the proxy name and select the credentials created in Step 1 and check the “PowerShell” check box and click on OK.

Step 3

Now create a JOB to delete the file.

Right-click on the Job node and click on New Job and enter the details and click on “Steps”.



After selecting the new steps in the job section, enter the name of the steps.

Select “PowerShell” as type and select “Run as ” proxy type and enter the following command line in the command section.

FORFILES /P "<<File Path>>" /M *.* /D -7 /C "cmd /c del @path” 2>&1 | find /v /i "ERROR: No files found with the specified search criteria.".

Now the preceding command deletes the file from the system.

• <<File Path>>: Enter the location where files are present
• -7: Number days prior to delete the files.
  
• 2>&1: Suppress the ERRORR: no file found error.

Click on OK.

After clicking on OK a job is created in the Jobs node, now right-click on Job and click on start job at steps.

After a successful check of the file location, all the prior, up to 7 days, will be deleted from the system.