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.

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

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 '12 at 8:52
  • If the stored procedure was remote, yes
    – gbn
    Jun 1 '12 at 9:02
4

Another option:

EXEC linkedserver.master.dbo.sp_executesql N'RESTORE DATABASE ...';
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 '15 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. Jun 9 '15 at 18:37
  • You need to say with move 'filename' to 'F:\path\file.mdf' - you can't just say to F:\ Jun 9 '15 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 '15 at 20:33

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.