Backing up and restoring remote database to and from a local disk

13 Aug 2006 6:10 PM

Peter Croft

Hi.
I am running Enterprise Manager for SQL Server 2000 on a local server and
accessing a database on a remote server.
How can I backup the database on the remote server to a disk on my local
server, and then restore from the local disk to the remote database later?
It seems to default to using the disk local to the database i.e. remote disk
for remote database.

Apologies if this is a very simple question but I can't find the answer
anywhere.
Thanks in advance for any help.
Peter Croft

13 Aug 2006 7:14 PM

Tibor Karaszi

The backup file is written by the SQL Server process, and it has no idea of what the disk looks like
on the client computer from where you run your client app. You can share a drive on the client
computer and address that as a UNC in the BACKUP command, but the Service Account used by the SQL
Server process need to have access to that UNC drive.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Peter Croft" <peter_cr***@textandvideo.co.uk> wrote in message
news:uUbl%23OwvGHA.3912@TK2MSFTNGP03.phx.gbl...
> Hi.
> I am running Enterprise Manager for SQL Server 2000 on a local server and
> accessing a database on a remote server.
> How can I backup the database on the remote server to a disk on my local
> server, and then restore from the local disk to the remote database later?
> It seems to default to using the disk local to the database i.e. remote disk
> for remote database.
>
> Apologies if this is a very simple question but I can't find the answer
> anywhere.
> Thanks in advance for any help.
> Peter Croft
>
>

13 Aug 2006 9:46 PM

Hari Prasad

To addon to Tibor;

Steps:-

1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services

Now you can execute the Backup script with UNC path

BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init

Note:

Backup to remote machine will not work if you start SQL server using Local
system account

Thanks
Hari
SQL Server MVP

Show quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:OHhlzywvGHA.1424@TK2MSFTNGP02.phx.gbl...
> The backup file is written by the SQL Server process, and it has no idea
> of what the disk looks like on the client computer from where you run your
> client app. You can share a drive on the client computer and address that
> as a UNC in the BACKUP command, but the Service Account used by the SQL
> Server process need to have access to that UNC drive.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Peter Croft" <peter_cr***@textandvideo.co.uk> wrote in message
> news:uUbl%23OwvGHA.3912@TK2MSFTNGP03.phx.gbl...
>> Hi.
>> I am running Enterprise Manager for SQL Server 2000 on a local server and
>> accessing a database on a remote server.
>> How can I backup the database on the remote server to a disk on my local
>> server, and then restore from the local disk to the remote database
>> later?
>> It seems to default to using the disk local to the database i.e. remote
>> disk
>> for remote database.
>>
>> Apologies if this is a very simple question but I can't find the answer
>> anywhere.
>> Thanks in advance for any help.
>> Peter Croft
>>
>>
>