Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Struggling to execute PowerShell commands in SqlServer Agent job of MS SQL Server 2012 R2 (Windows Server 2008R2, Windows 7 Prof)

I need to delete (.bak files older, than, say, 7 days in remote file share.

Before inserting the command into SqlServerAgent job, I tried to execute it from Windows command line:

  • using forfiles :
    forfiles /P "\\RemoteHostName\sharedFolderName" /s /m *.bak /D -7 /C "cmd /c del @path"

    gives the error:
    "ERROR: UNC paths (\machine\share) are not supported"

  • using Windows PowerShell:

    dir \\RemoteHostName\sharedFolderName |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del

The latter deletes the required remote files when used from Windows PowerShell command prompt but when executed in/from an SqlServerAgent job - fails

The history of such SqlServerAgent jobs shows, quoting:

  • The job succeeded. The job was invoked by User domain\domainAccountName. The last step to run was step 1

    • Executed as user: localHostName\sqlbackup. The job script encountered the following errors. These errors did not stop script: A job step received an error at line in a PowerShellScript. The corresponding line is 'dir "\\RemoteHostName\sharedFolderName\" |? {$_.CreationTime -lt (get-date).AddDays(-7)} | del'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find path '\\RemoteHostName\sharedFolderName' because it does not exist. ' Process Exit Code 0. The step succeeded.

    enter image description here

Note the green check-marks on failed job and job steps

Again, zoomed-in the text output of the result:

enter image description here

Also, when executed this SqlServerAgent job step shows: "processing", then "Success"

enter image description here

though it is persistingly failing to delete required files.

The question(s):

  1. How to make the job and step, containing errors, to show "Failed" instead of of "Succeeded"?
  2. Why does the purging of older files using Windows PowerShell commands succeedes from command line prompt and fails from within SqlServerAgent job&step?

    2a. How succeed with removing of older remote files on schedule from SQL Server 2012?

I am using SQL Server Agent Job proxy according to this answer using local Windows user accounts on remote/target and local/source hosts with the same/equal names sqlbackup and the passwords

The same story with scheduling in Windows Task Scheduler - the job and steps are "succeeding" without deleting anything though copying to remote location (out of description here) by previous steps are successful. And I am interested to centralize all in SQL Server SqlServerAgent job (but not outside)

share|improve this question
Check this question:… – Shawn Melton Oct 25 '13 at 8:56
For question #1 check this SO question… – Bruce Oct 25 '13 at 14:53
@Bruce, I've solved the question #1 rather fast but I cannot delete remote files from SQL Server Agent job while it is successful from command prompt by the same commands – Gennady Vanin Геннадий Ванин Oct 29 '13 at 4:48

In order to make PowerShell errors cause a SQL Agent step to fail you have to make an adjustment to the $erroractionpreference setting. By default this value is set to Continue so the steps will show successful because no error is raised up for SQL Server Agent to detect an issue in the actual PowerShell command occurred.

You can put this at the top of your script that should fix that: $erroractionpreference="stop". This will allow the error PowerShell is returning to trickle up to SQL Server Agent process.

share|improve this answer

Forfiles doesn't work with UNC. The trick is to map the shared UNC using pushd. This maps and entered a temporarily drive. Popd will

pushd \RemoteHostName\sharedFolderName forfiles /P "\RemoteHostName\sharedFolderName" /s /m *.bak /D -7 /C "cmd /c del @path" popd

share|improve this answer

Use a domain account for running the SQL Server Agent. Give that account rights modify rights on the network share with the files you want to delete.

share|improve this answer
Thanks for your answer but I have no choice of meddling with services and accounts. I run SQL Server Agent unde NT Service\SQLSERVERAGENT and, even more, I have to use a local account, so I've configured a credential and SQL Server Agent proxy. I wrote this under "PS", at the end of my question – Gennady Vanin Геннадий Ванин Nov 1 '13 at 3:54

I have achieved this(How to delete the remote files older than N days on schedule from SQL Server 2012 (with SqlServerAgent job).

I have been running backups of SSAS databases, and needed to delete the old backup files, on a different server.

Please see run-a-powershell-script-on-a-different-server-from-inside-a-sql-server-job

share|improve this answer

Create a maintenance plan for deleting backups from folder having age greater than N days

Create Maintenance Cleanup Task > Select connection type as a User having sufficient rights(sysadmin user credentials) > Select the folder location containing backups > select the days or weeks till which you want the backups > OK

Now schedule this plan on daily/weekly basis.....

T-SQL created by maintenance plan is

EXECUTE master.dbo.xp_delete_file 0,N'BACKUP FOLDER LOCATION',N'bak',N'DATE FOR N DAYS T TIME ',1

But above script doesn't work properly if executed in query window, So use Maintenance plan.......

share|improve this answer
This does not work, unless I misunderstood it. How exactly in the maintenance plan? do you have an example of it working? – marcello miorelli Aug 12 '15 at 15:37

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.