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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Restoring a database a Server using a Remote...

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

Rate Topic

Display Mode

Topic Options

Author

Message

nawillia

Posted Friday, January 04, 2013 2:55 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:05 AM
Points: 89, Visits: 387

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

Post #1403137

@SQLFRNDZ

Posted Friday, January 04, 2013 5:27 PM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 12:03 PM
Points: 412, Visits: 862

nawillia (1/4/2013)

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.

--SQLFRNDZ

Post #1403166

opc.three

Posted Saturday, January 05, 2013 3:15 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 11:14 PM
Points: 6,706, Visits: 11,738

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1403280

nawillia

Posted Tuesday, January 08, 2013 8:02 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:05 AM
Points: 89, Visits: 387

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

Post #1404262

« Prev Topic | Next Topic »

Permissions