Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
add comment

2 Answers

up vote 3 down vote accepted

OK, that was pretty silly.

EXEC ('RESTORE DATABASE ...')
AT other_server_name
share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.