We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:
> That is a good question, one I have asked also. We currently have a 2 node
> active passive cluster (I know very little about the network side of things,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the office
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> >
> >
> > Sounds like your NAS solution is insufficient. If you can't move your
> > backup file to a new server in a reasonable amount of time, how would you
> > ever recover in case of a disaster?
> >
> >
> > David
> >
>
>|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
>> That is a good question, one I have asked also. We currently have a 2
>> node
>> active passive cluster (I know very little about the network side of
>> things,
>> and I think the net admin likes it that way).
>> The NAS size is 450 gb for "everything", all the databases, etc. It
>> isn't a
>> fibre channel but he says the throughput (?) is comparable, so that
>> shouldn't be the bottleneck.
>> I forgot to mention that the production server and NAS are in one domain,
>> (on the West coast) to a server on another domain (we have several
>> domains
>> because the production domain is W2k3 and the others are W2k) at the
>> office
>> here on the East Coast.
>> All this was done shortly before I came on board, we have no DBA, I am
>> serving as SQL DBA/Developer as well as the Access developer.
>> Any ideas on a better solution?
>> Nancy
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>> >
>> >
>> > Sounds like your NAS solution is insufficient. If you can't move your
>> > backup file to a new server in a reasonable amount of time, how would
>> > you
>> > ever recover in case of a disaster?
>> >
>> >
>> > David
>> >
>>|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
--
Thanks,
David
"Nancy Lytle" wrote:
> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours because
> the method he is using now is to compress the .bak file using winrar and he
> expects that to take 13 hours total to compress the database and chop it up
> into 100MB chunks using winrar. He has paused this process until off hours
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't seem
> quite right. I requested this be done over 2 weeks ago, and the request has
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> > One or two questions you may not be able to answer:
> > Are both the source and target servers using the same NAS? If so, is
> > there
> > an NAS utility that can duplicate the files? (Split a mirror and re-attach
> > the mirror to the other server, for example--a technique we used to move a
> > multi-terabyte db.)
> >
> > Is your problem in the backup or the transfer? If it is the transfer, how
> > are you doing the transfer, ie, are you on a private,dedicated link or
> > using
> > the Internet (since your source and destination are 3K miles apart.)
> >
> > What is unacceptable timing? For a 50GB database, I would not be
> > surprised
> > at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> > handshaking, etc).
> >
> > If you have a high capacity tape drive, it might be faster to backit up to
> > tape and then overnight the tape to the destination.
> >
> >
> > --
> > Joseph R.P. Maloney, CSP,CCP,CDP
> >
> >
> > "Nancy Lytle" wrote:
> >
> >> That is a good question, one I have asked also. We currently have a 2
> >> node
> >> active passive cluster (I know very little about the network side of
> >> things,
> >> and I think the net admin likes it that way).
> >> The NAS size is 450 gb for "everything", all the databases, etc. It
> >> isn't a
> >> fibre channel but he says the throughput (?) is comparable, so that
> >> shouldn't be the bottleneck.
> >> I forgot to mention that the production server and NAS are in one domain,
> >> (on the West coast) to a server on another domain (we have several
> >> domains
> >> because the production domain is W2k3 and the others are W2k) at the
> >> office
> >> here on the East Coast.
> >> All this was done shortly before I came on board, we have no DBA, I am
> >> serving as SQL DBA/Developer as well as the Access developer.
> >>
> >> Any ideas on a better solution?
> >> Nancy
> >> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> >> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> >> >
> >> >
> >> > Sounds like your NAS solution is insufficient. If you can't move your
> >> > backup file to a new server in a reasonable amount of time, how would
> >> > you
> >> > ever recover in case of a disaster?
> >> >
> >> >
> >> > David
> >> >
> >>
> >>
> >>
>
>|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
--
Thanks,
David
"david" wrote:
> Why don't you try moving the file using FTP rather than copy. If you have a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You can
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the file.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB and
> FTP takes about 20 minutes to move the file. So you can cut the time down to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:
> > The source is on a NAS, the destination is not.
> > The backup is fine, it is the transfer.
> > As to the time involved, I'm guessing it was longer than 36-48 hours because
> > the method he is using now is to compress the .bak file using winrar and he
> > expects that to take 13 hours total to compress the database and chop it up
> > into 100MB chunks using winrar. He has paused this process until off hours
> > since winrar is very cpu intensive. He expects this to be completely
> > archived in 2 days. Then he is going to transfer (so I am guessing the
> > winrar is being done on the NAS) the files via cable modem (since it is
> > about 7 times faster than the office T1 and will not interfere with our
> > email or phones during business hours). He is then going to burn the file
> > to a CD and bring it in for me.
> >
> > As I mentioned earlier, there's just something about this that doesn't seem
> > quite right. I requested this be done over 2 weeks ago, and the request has
> > still not been completed.
> >
> > Thanks for you insight.
> >
> > Nancy
> > "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> > news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> > > One or two questions you may not be able to answer:
> > > Are both the source and target servers using the same NAS? If so, is
> > > there
> > > an NAS utility that can duplicate the files? (Split a mirror and re-attach
> > > the mirror to the other server, for example--a technique we used to move a
> > > multi-terabyte db.)
> > >
> > > Is your problem in the backup or the transfer? If it is the transfer, how
> > > are you doing the transfer, ie, are you on a private,dedicated link or
> > > using
> > > the Internet (since your source and destination are 3K miles apart.)
> > >
> > > What is unacceptable timing? For a 50GB database, I would not be
> > > surprised
> > > at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> > > handshaking, etc).
> > >
> > > If you have a high capacity tape drive, it might be faster to backit up to
> > > tape and then overnight the tape to the destination.
> > >
> > >
> > > --
> > > Joseph R.P. Maloney, CSP,CCP,CDP
> > >
> > >
> > > "Nancy Lytle" wrote:
> > >
> > >> That is a good question, one I have asked also. We currently have a 2
> > >> node
> > >> active passive cluster (I know very little about the network side of
> > >> things,
> > >> and I think the net admin likes it that way).
> > >> The NAS size is 450 gb for "everything", all the databases, etc. It
> > >> isn't a
> > >> fibre channel but he says the throughput (?) is comparable, so that
> > >> shouldn't be the bottleneck.
> > >> I forgot to mention that the production server and NAS are in one domain,
> > >> (on the West coast) to a server on another domain (we have several
> > >> domains
> > >> because the production domain is W2k3 and the others are W2k) at the
> > >> office
> > >> here on the East Coast.
> > >> All this was done shortly before I came on board, we have no DBA, I am
> > >> serving as SQL DBA/Developer as well as the Access developer.
> > >>
> > >> Any ideas on a better solution?
> > >> Nancy
> > >> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > >> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> > >> >
> > >> >
> > >> > Sounds like your NAS solution is insufficient. If you can't move your
> > >> > backup file to a new server in a reasonable amount of time, how would
> > >> > you
> > >> > ever recover in case of a disaster?
> > >> >
> > >> >
> > >> > David
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >
