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.