Backup/copy to remote server

Hello,
I've spent the afternoon reading old newsgroup posts and BOL but am
still a bit confused about the best way to approach a new backup
scheme.
My organization would like to establish a single repository for backups
from our database servers, by either backing up databases on each
server and then moving them to the new, central server in some
automated fashion, or by simply pointing to the remote server as the
backup device.

>From a network performance standpoint I think backing up locally and
then copying the resulting files to a remote destination may be the
better option, as we have several servers and at least one of the
databases residing on them results in an almost 2GB backup file (with
most of the remainder smaller than that). I'm also unsure of exactly
how to go about this.
I believe SQL Server has been installed by default under the local
system account, so I'm wondering if this will limit my options with
regard to authentication across servers.
I've read about the BACKUP command, "log shipping" and other options,
but am not sure if any match our requirements.
If anyone could shine a light down the right path for me, my blood
pressure would be grateful. ;)
vast55555@.yahoo.com wrote:
> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>
Create a share on your central server, configure SQL to run as a domain
user, grant that domain user permissions to the share, then use the
BACKUP command to backup directly to the UNC for that share. We move a
dozen backups around on our network each night, some of which are
approaching 200GB, there is no noticeable difference between backing up
locally and then copying vs. backing up over the network.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi Tracy,
Thanks for your quick response. That sounds like the way to go. One
potential problem is that this is a hosted situation--not in-house--and
I think there may be no domain, but rather isolated machines (I'm not a
network engineer by trade so I'm a bit ignorant here).
Tracy McKibben wrote:
> Create a share on your central server, configure SQL to run as a domain
> user, grant that domain user permissions to the share, then use the
> BACKUP command to backup directly to the UNC for that share. We move a
> dozen backups around on our network each night, some of which are
> approaching 200GB, there is no noticeable difference between backing up
> locally and then copying vs. backing up over the network.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Performance wise, backing up straight to a remote file share is often faster
than backing up locally and then copying the backup file to the same file
share. But there are other factors to consider. For instance, with backing up
straight to the remote file share, if it fails towards to the end due to some
network issue, the retry may take longer than just retrying the copy
operation.
Linchi
"vast55555@.yahoo.com" wrote:

> Hello,
> I've spent the afternoon reading old newsgroup posts and BOL but am
> still a bit confused about the best way to approach a new backup
> scheme.
> My organization would like to establish a single repository for backups
> from our database servers, by either backing up databases on each
> server and then moving them to the new, central server in some
> automated fashion, or by simply pointing to the remote server as the
> backup device.
> then copying the resulting files to a remote destination may be the
> better option, as we have several servers and at least one of the
> databases residing on them results in an almost 2GB backup file (with
> most of the remainder smaller than that). I'm also unsure of exactly
> how to go about this.
> I believe SQL Server has been installed by default under the local
> system account, so I'm wondering if this will limit my options with
> regard to authentication across servers.
> I've read about the BACKUP command, "log shipping" and other options,
> but am not sure if any match our requirements.
> If anyone could shine a light down the right path for me, my blood
> pressure would be grateful. ;)
>