I'm trying to write a script to copy the last 'good set' of MS 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 serverServer
- 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'mI'm assuming that it's a permissions issue - what user does the agent job run as when scheduled? MaybeMaybe it's the user that i'mI'm logged in as thats used when running it manually?
Either that, or is there a better way of doing it? II don't really want to run the backup twice as there's around 1TB of data to back up.
