Home / server / Questions / 18621
Accepted
Richard Gadsden
Asked: 2009-06-04 03:21:20 +0800 CST

SQL 2005 RESTORE DATABASE on a linked server

  • 772

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.

sql-server sql-server-2005

2 Answers

  1. Best Answer

    OK, that was pretty silly.

    EXEC ('RESTORE DATABASE ...')
AT other_server_name
    • 3

  2. 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.

    • 2