Restoring DB on a Remote Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Restoring DB on a Remote Server

Author

Topic

Mmats
Starting Member

47 Posts

Posted - 2005-07-19 : 11:56:28

I am trying to find a way to do a query on a remote server to restore a database. Here is what I am currently trying:

SELECT * FROM OPENROWSET('','192.168.1.200';'sa';'sa',
'RESTORE DATABASE Test FROM DISK = "e:\program files\mssql\backup\db backups\database.BAK"'

The problem I run into is it doesnt like the double quotes around the file path, and it wont let me use single quotes. If openrowset accepted variables, I wouldnt have this problem. The reason I went with openrowset is because I dont want to add a linked server and I need to do this remotely. Running w2k server, sql2000.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-19 : 12:22:22

I do not think this will work even of you properly escaped your qoutes which is possible.

I have an offsite server I run backups on. What I do is I backup the database locally and then I have a bat file that I execute from xp_cmdshell that does the command line ftp action to get it to where I want it to go. You may want to do some similar here. If you want the code I will send it to you.

BTW, giving a bunch of people your IP address and your sa account password over the internet is a bad idea. A sa password of sa is a bad idea. Waiting to be hacked.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

Mmats
Starting Member

47 Posts

Posted - 2005-07-19 : 12:29:38

Thats not the real ip address or password

. But I am basically doing the same thing you described, copying the database with xp_cmdshell to a determined location on the remote server and then restoring it. The only problem Im having is with the quotes on the restore query. Just so you know, the filepath in that query resides on the remote server, so it is correct.

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-07-19 : 12:42:03

Have you tried nameing your provider. This just parsed for me:

SELECT * FROM OPENROWSET('SQLOLEDB','192.168.1.200';'sa';'sa',
'RESTORE DATABASE Test FROM DISK = "e:\program files\mssql\backup\db backups\database.BAK"')

But your orignal did not.

My problem with this is that I have not seen OPENROWSET used for anything but to return data. If my suspicion is correct I suppose you could wrap your RESTORE command in a stored procedure and have the last command in the sp be something like

SELECT 'it worked' as Result

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.

I am available for consulting work. Just email me through the forum.

Mmats
Starting Member

47 Posts

Posted - 2005-07-19 : 13:30:23

Well I just saw that I was missing the closing parenthesis on my statement, so now Im running this:

SELECT * FROM OPENROWSET('SQLOLEDB','192.168.1.200';'sa';'sa',
'RESTORE DATABASE Test FROM DISK = "e:\program files\mssql\backup\db backups\database.BAK"')

And it gives me this error:

Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'e:\program files\mssql\backup\db backups\database.BAK'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

Kristen
Test

22859 Posts

Posted - 2005-07-20 : 13:33:46

You've got a double quote around your filename, it probably needs to be a doubled-up single quote.

But I'm sceptical that it will work.

Physically connect to the [192.168.1.200] server, and open Query Anaylyser

Find a restore command that works - perhaps something as simple as:

RESTORE DATABASE Test FROM DISK = 'e:\program files\mssql\backup\db backups\database.BAK'

will. Once you've done that double-up the single quotes and try that in your original command.

In my experience there are problems with paths that include spaces - putting the file you want to restore on a path that has NO spaces in the name might help; on a point of procedure I would strongly recommend that you do NOT store data/backup files anywhere under a path that includes "...\program files\..." in the name.

Kristen

Mmats
Starting Member

47 Posts

Posted - 2005-07-20 : 15:31:02

Ok you were right about the quotes, I got it to work with:
SELECT * FROM OPENROWSET('SQLOLEDB','192.168.1.200';'sa';'sa',
'RESTORE DATABASE Test FROM DISK = ''e:\database.BAK'' select 1')

This actually returns an error, yet restores the db. Without the select statement at the end('select 1') it will bring back the same error msg but it WONT restore the db. The error msg is:
Server: Msg 7357, Level 16, State 1, Line 1
Could not process object 'RESTORE DATABASE Test FROM DISK = 'e:\database.BAK' select 1'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=RESTORE DATABASE Test FROM DISK = 'e:\database.BAK' select 1'].

I cant get it to work with spaces in the file path but I can live with that. Would rather not have that error msg but the job is still getting done. Thanks for the help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 15:48:55

You'd be able to edit your posts if you were logged into the forums. Check the top of this page to see if you are logged in.

Tara

Mmats
Starting Member

47 Posts

Posted - 2005-07-20 : 16:54:25

Well it seems when I stick this into a stored procedure, it doesnt go past the openrowset call, even though it restores the db. It keeps returning the error I mentioned above and then quits.

Mmats
Starting Member

47 Posts

Posted - 2005-07-20 : 17:42:00

For those interested, found the answer, and it works with spaces in the filepath and gives no error msg. Used a different provider:

SELECT * from OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=192.168.1.200;UID=sa;PWD=sa','RESTORE DATABASE Test FROM DISK = ''e:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\DB backups\database.BAK'' select 1')

Again, you need a select statement after the restore but it gets the job done. No need to set up linked servers. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources