1

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:

  1. Back up the target databases
  2. Call an external process to copy the created backup files
  3. Clean up any backup older than 2 days (runs at the same time as 2)
  4. 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.

Improve this question
2
  • @aasim.abdullah Cheers. I looked into this a bit further and apparently CmdExec doesn't wait for the command to finish, it just reports having successfully started it. I'll have to come up with a workaround, maybe putting the final step into the same SQL Agent job as the transfer.
    – toryan
    Commented Mar 17, 2015 at 6:37
  • Look into doing it in powershell as a job step, I believe the job step will not proceed until the script finishes but I haven't had a chance to test yet.
    – Your_comment_is_not_funny
    Commented Mar 18, 2015 at 16:24

1 Answer 1

Reset to default
0

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)
Improve this answer
4
  • Where do I put this? In the restore job on the dev server? What happens when this runs and the file does not exist?
    – toryan
    Commented Mar 17, 2015 at 8:39
  • I just put an example code for your "Restore" job step. Change it accordingly.
    – Aasim Abdullah
    Commented Mar 17, 2015 at 9:16
  • You could even have the ELSE make the process wait a few minutes and then try the restore again.
    – Zane
    Commented Mar 17, 2015 at 19:46
  • You don't need an else if this is part of a sql agent...just set the retry number and wait time.
    – Steve Mangiameli
    Commented Mar 18, 2015 at 21:28

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.