Executing a RESTORE across servers

14 Jul 2005 8:59 PM

E2TheC

I have a procedure in a db on Server A that I want to have execute a RESTORE
statement to restore a database on Server B. Is this possible
programmatically?

Server B is a Linked Server on Server A, but how can I execute the RESTORE?

Thanks,

E2TheC

14 Jul 2005 9:21 PM

Hari Prasad

Hi,

Linked servers are used to access and Manipulate data between servers. In
the case of RESTORE you are not going to manipulate
any thing across cross server databases. So please execute the RESTORE
DATABASE command from SERVER B itself.
Or schedule a talk using SQL Agent jobs using Enterprise manager of SERVER
B.

Thanks
Hari
SQL SERVER MVP

Show quote

"E2TheC" <E2T***@discussions.microsoft.com> wrote in message
news:1E2123D0-94A6-41B8-B4F9-462F91703029@microsoft.com...
>I have a procedure in a db on Server A that I want to have execute a
>RESTORE
> statement to restore a database on Server B. Is this possible
> programmatically?
>
> Server B is a Linked Server on Server A, but how can I execute the
> RESTORE?
>
> Thanks,
>
> E2TheC

14 Jul 2005 9:48 PM

E2TheC

Thanks. Not an option, but thanks for the reply.

I was able to make it work by using sp_executesql and passing the restore
statement as an nvarchar string.

Example:

declare @restore_string nvarchar(1000)

set @restore_string = N'{restore statement here}'

exec linked_servername.master.dbo.sp_executesql @restore_string

Cheers.

Show quote

"Hari Prasad" wrote:

> Hi,
>
> Linked servers are used to access and Manipulate data between servers. In
> the case of RESTORE you are not going to manipulate
> any thing across cross server databases. So please execute the RESTORE
> DATABASE command from SERVER B itself.
> Or schedule a talk using SQL Agent jobs using Enterprise manager of SERVER
> B.
>
> Thanks
> Hari
> SQL SERVER MVP
>
>
> "E2TheC" <E2T***@discussions.microsoft.com> wrote in message
> news:1E2123D0-94A6-41B8-B4F9-462F91703029@microsoft.com...
> >I have a procedure in a db on Server A that I want to have execute a
> >RESTORE
> > statement to restore a database on Server B. Is this possible
> > programmatically?
> >
> > Server B is a Linked Server on Server A, but how can I execute the
> > RESTORE?
> >
> > Thanks,
> >
> > E2TheC
>
>
>