Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to copy a database from one server to another by backup and restore.

I've created steps in an agent job to backup the database to file and to xcopy the file over to the other server.

I'm trying to do the restore now, and it's not working. The servers are linked, and the job is running on the source server.

I tried to do RESTORE DATABASE, but it doesn't seem to do two-part naming (server and database) when doing a restore.

I've also tried using OPENQUERY but that doesn't work either, I think because RESTORE DATABASE has neither inputs or outputs.

share|improve this question
up vote 3 down vote accepted

OK, that was pretty silly.

AT other_server_name
share|improve this answer

That works fine, but it also is an issue if the source server job fails or the network hiccups. I'd recommend you create the restore job on the destination server. You can still kick off the job with EXEC ..AT or with sp_start_job from the source server, but you've removed a dependency here. It also allows you to schedule the restore (if this is a regular occurrence) on the destination server.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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