4

Is it possible to restore a database on a server, using a script on another server?

For example, I have a server A and a server B and I want to restore a database foo on server B from within a script on server A.
I was thinking of something like this: RESTORE DATABASE B.foo FROM DISK ..., but I can't find any information about it.

And I'm looking for something similar for BACKUP DATABASE too.

Improve this question

2 Answers 2

Reset to default
1

No. Most commands are specific to the local server.

Saying that, if you have a linked server, you can do something like this:

SELECT * FROM OPENQUERY(MyLinkedServer, 'RESTORE DATABASE foo FROM DISK ...; SELECT ERROR_MESSAGE();')

Edit, after comments

You could also run EXEC MyLinkedServer.SomeDB.SomeSchema.MyRestoreWrapper ...

Improve this answer
2
  • While reading documentation on OPENQUERY, I found that you can also execute a stored procedure on a linked server. So if I were to put my restore script in a stored procedure, this should have the same result?
    – Stijn
    Jun 1, 2012 at 8:52
  • If the stored procedure was remote, yes
    – gbn
    Jun 1, 2012 at 9:02
4

Another option:

EXEC linkedserver.master.dbo.sp_executesql N'RESTORE DATABASE ...';
Improve this answer
4
  • Is there a way to use this, using "WITH MOVE..."? Because, let's say I'm on SQL1 ( E:\ for data files ), but I want to restore the database into SQL2 ( F:\ data files ). if I use MOVE 'Z' TO F:, it says that the server does not havethis Drive. It will work "...`sp_executesql N'RESTORE DATABASE ...MOVE xyz TO F:`" ?
    – Racer SQL
    Jun 9, 2015 at 17:39
  • 1
    Yes, you should be able to use WITH MOVE, but you need to add the argument for all files. I can't fix the code you have because, well, I can't see the code you have, nor do I know the drive layout of the destination server or the file specs for the database you're trying to restore.
    – Aaron Bertrand
    Jun 9, 2015 at 18:37
  • You need to say with move 'filename' to 'F:\path\file.mdf' - you can't just say to F:\
    – Aaron Bertrand
    Jun 9, 2015 at 18:39
  • Oh yes... I have the full Patch, it was just an example. I tested here and it worked very well. Thanks @aaron Bertnand
    – Racer SQL
    Jun 9, 2015 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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