A while back I asked for help automating a database restore using a backup file provided from another server: Automatically restore SQL Server database from file from another server. I'm pleased to say that I now have this working as a SQL Server Agent job.
We are now trying to trigger this job as part of a maintenance plan that runs on the other (dev) server. At the moment we have a maintenance plan that runs on the live server and performs the following steps, in sequence:
- Back up the target databases
- Call an external process to copy the created backup files
- Clean up any backup older than 2 days (runs at the same time as 2)
- Start the restore job on the other server
When I run the maintenance plan on live it reports success, however when we check the job history on the dev server it shows that it has failed saying that it could not find the file. Running the job again returns success, and a manual check indicates that the files have been copied correctly.
It would seem that the restore job is being started too soon but I can't figure out why. Is there any reason why the restore job is being triggered before the copy process has completed?
EDIT: I've checked the maintenance plan logs and the restore job step starts 1 second after the transfer step. Obviously the transfer takes longer than this, so I can at least see evidence of the problem.