Posted by Erland Sommarskog on 06/10/05 00:06

mbailey (matt.bailey@gmail.com) writes:
> I have a Sql Server 2000 database offsite that I would like to back up
> to a local machine. I am using Enterprise Manager on a local machine to
> administer the remote db.
>
> Whats the best way to schedule backups so the remote db is backed up to
> the local machine?
>
> Can this be done in Enterprise Manager?

Yes, you would have to schedule the job so that it runs against the
remote server. (But you could have the job run through the local
SQL Server Agent, though.)

But there is one requirement: the remote SQL Server must have write
access to the disk where you want the backup to end up. First of all,
this means that the remote server must run under a domain account; it
cannot run under Local System.

Since your server is offsite, it is not obvious that the remote
server has access to your local box. And in any case, if the network
connection is not the best, it may not be a good idea anyway.

In such case you will have to explore other options, such as backing
up to disk local to the remote server, and then get the files with FTP.
The job could still be initiated from your local server, though.

> Would you recommend any 3rd party software?

The two most commonly used 3rd party tools are SQL Litespeed from
Imceda and UltraBac. I believe that they are mainly of interest for
large databases from 100 GB where backup times can be considerable.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]