T-SQL script doesn't delete files when run as an agent job - does if run manually I'm trying to write a script to copy the last 'good set' of SQL Server backup files to another drive (it's a long story - we're using a suppliers R1Soft for backup and it won't allow us to exclude folders when we're talking about ~1M files).

The idea behind the script is that it:


Grabs the last backup location from SQL Server
Deletes the file from the separate drive
Runs Ola Hallengrens script to run a backup
Grabs the last backup location again (so now it's the one just made)
Copies that file to the separate drive


The problem that i'm having is that everything runs ok if i'm running the agent job manually, but when it comes to have the job run as scheduled, it does the copy part at the end, but doesn't do the initial delete - so all the old files stay on the drive and have to be deleted manually.

There's nothing in the logs that indicate what could be happening (no failures or error messages). I'm assuming that it's a permissions issue - what user does the agent job run as when scheduled? Maybe it's the user that I'm logged in as thats used when running it manually?

Either that, or is there a better way of doing it? I don't really want to run the backup twice as there's around 1TB of data to back up.
The account that you use to run the job WILL need permissions to delete those files. To test your process, login as that specific user and test your job--specifically the deletion of files-- with that user.

That being said, I've had issues purging SQL Server backups the way I'd like so I created this CmdExec script do so. To delete files and get error information back, consider running this as one of your job steps (should/has to be a one liner for CmdExec to work right):

cmd /e:on /c "forfiles /p "c:\MYDATABASEBACKUPS" /s /m *.BAK /d -2 /c "cmd /c del @path" 2&gt;&amp;1 |  findstr /V /O /C:"ERROR: No files found with the specified search criteria."2&gt;&amp;1 | findstr ERROR&amp;&amp;EXIT 1||EXIT 0"&amp;exit %errorlevel%


This script purges backups based on how old they are in number of days. The /d -2 tells the script to keep two days worth. Change 2 to whatever number you'd like to keep.

Also, change c:\MYDATABASEBACKUPS to your backup location. Change *.BAK to the backup extension of your backup files. The "fanciness" of this script is because I get rid of the false positive "No files found"--because sometimes there won't be files to find.

Good luck with your scripting!