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.


OK, that was pretty silly.

AT other_server_name
| 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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