backup database on another server as instance

> You cannot backup a database and directly restore it. Rather, you back it
> up to a file somewhere and then have the other instance restore it. In
> your
> case, PC1 should have a file share to which the service account on Server1
> has read-write access. This account must be a domain account - not a
> local
> one. Your MSDE instance on PC1 can then do a restore from that file.

I'm not really worried about restoring it. That will probably be done later
through EM. I really just want to make a backup file of the database of an
instance on another server from PC1. So on PC1 I'm connected to a local
instance called PC1\Billing. How do a structure a BACKUP DATABASE command to
allow me to backup the instance on SERVER1 to the local drive of PC1
provided the SQL instance on SERVER1 is a domain account with write access
to the backup share on PC1? Can I do it directly in a script or stored proc
on PC1 or do I need to create something on SERVER1 and call it from PC1? If
that's the case, what is the best way to do it?

TIA

Matt
 >> Stay informed about: backup database on another server as instance 

Matt Williamson (ih8spam@spamsux.org) writes:
> I'm not really worried about restoring it. That will probably be done
> later through EM. I really just want to make a backup file of the
> database of an instance on another server from PC1. So on PC1 I'm
> connected to a local instance called PC1\Billing. How do a structure a
> BACKUP DATABASE command to allow me to backup the instance on SERVER1 to
> the local drive of PC1 provided the SQL instance on SERVER1 is a domain
> account with write access to the backup share on PC1? Can I do it
> directly in a script or stored proc on PC1 or do I need to create
> something on SERVER1 and call it from PC1? If that's the case, what is
> the best way to do it?

On SQL 2000, you cannot easily initiate a BACKUP operation on different
instance. You need to be connected to that instance. Or at least have
a stored procedure on the remote instance that performs the back that
you can call.


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: backup database on another server as instance 

Why do you need to connect to the local instance? Simply uses EM, QA, or
osql on your PC to connect to the remote instance and initiate from there.
Just be sure to use the full UNC name of the share - not a drive letter.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Matt Williamson" wrote in message

> You cannot backup a database and directly restore it. Rather, you back it
> up to a file somewhere and then have the other instance restore it. In
> your
> case, PC1 should have a file share to which the service account on Server1
> has read-write access. This account must be a domain account - not a
> local
> one. Your MSDE instance on PC1 can then do a restore from that file.

I'm not really worried about restoring it. That will probably be done later
through EM. I really just want to make a backup file of the database of an
instance on another server from PC1. So on PC1 I'm connected to a local
instance called PC1\Billing. How do a structure a BACKUP DATABASE command to
allow me to backup the instance on SERVER1 to the local drive of PC1
provided the SQL instance on SERVER1 is a domain account with write access
to the backup share on PC1? Can I do it directly in a script or stored proc
on PC1 or do I need to create something on SERVER1 and call it from PC1? If
that's the case, what is the best way to do it?

TIA

Matt
 >> Stay informed about: backup database on another server as instance