Restoring a database a Server using a Remote Server as the Service call

Question

Restoring a database a Server using a Remote Server as the Service call

nawillia

SSCrazy

Points: 2401
More actions
January 4, 2013 at 2:55 pm

#286984

My coworker are brainstorming some ideas because our network is divided geographically.
One question we are pondering is if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

nawillia (1/4/2013)
My coworker are brainstorming some ideas because our network is divided geographically.
One question we are pondering is if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Thanks

Ping server B from server A to see if it can find server B in the network and then you should be able to conncet to server B from server A and you could restore a database on Server B.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

The RESTORE command has to execute on the instance where the database will be restored. I would recommend using PowerShell for a job like this. You can use the SQL Provider to issue a restore command. Working in the context of a PowerShell session you can also negotiate copying the backup file somewhere if needed, or cleaning up old copies of backups, without jumping through any hoops trying to set something like that up in T-SQL.

If you want or need to do everything in T-SQL then you could setup a Linked Server on ServerA pointing to ServerB and issue the RESTORE command using EXEC()...AT on ServerA:

EXEC('RESTORE DATABASE...') AT [ServerBLinkedServerName];

edit: spelling

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

nawillia SSCrazy Points: 2401 More actions · Answer 3

Thanks.

That worked.

For future searchers, here is the msdn link http://msdn.microsoft.com/en-us/library/ms188332.aspx

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 4

Can you give a full example of what this would look like?

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh