Automatic database restore fails as cannot find file 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.
Copy step of your job will just inform operating system to start copy process and will mark this step completed and this will initiate next step "Restore Database", but meanwhile operating system is still copying your file to destination. 

Before restore, you must verify file existence first. 

Declare @file_exists int
EXEC xp_fileexist 'c:\MyDatabase.bak', @file_exists out
IF @file_exists = 1
BEGIN
  RESTORE DATABASE ....................
END
ELSE
  RAISERROR ('Restore Job Step Failure!', 16, 1)