1

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.

Improve this question
5
  • Probably the service account that has not got rights to delete on your folder. Your account probably has try running it with a proxy with your account and see if that works.
    – Stijn Wynants
    Commented Apr 14, 2016 at 14:01
  • is your jobs separated by steps? did you check if the "go to the next step" is checked? can you copy the code here for us ?did you check the database on your job ?
    – Racer SQL
    Commented Apr 14, 2016 at 14:02
  • Check this answer. Please stop using T-SQL to perform file copy/delete/move tasks. PowerShell is the way to go. Check the job owner as well as service account permission + enable logging to text file which will help you find out the cause.
    – Kin Shah
    Commented Apr 14, 2016 at 14:03
  • @Kin - I take it that it's possible to use Powershell to retrieve the last backup file of each type then? I'll check it out. I'm new to DB admin, so learning all the time!
    – leighjepson
    Commented Apr 14, 2016 at 15:07
  • @RafaelPiccinelli Yeah, it's seperated by steps, but the steps are progressing correctly (the logs show this). No DB checking as part of the job. I'm on my mobile at the moment so will post my (albeit atrocious) code shortly.
    – leighjepson
    Commented Apr 14, 2016 at 15:10

1 Answer 1

Reset to default
1

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>&1 |  findstr /V /O /C:"ERROR: No files found with the specified search criteria."2>&1 | findstr ERROR&&EXIT 1||EXIT 0"&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!

Improve this answer
2
  • Permissions was the first thing that came to mind, especially if doing it manually works. That generally eliminates the code as the culprit and you are looking at local or domain permissions from there.
    – Steve Mangiameli
    Commented Apr 14, 2016 at 18:57
  • Indeed! It's a pain but you can enable auditing within Windows and audit the specific directory you are trying to delete from to get O/S errors the SQL Agent might not show. I detest doing this but have had to from time to time...
    – Sting
    Commented Apr 14, 2016 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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