Hello,
I copied a backup file of a production DB on a production server to another
location on a Development server. When I try to restore this backup on the
Dev server - from Enterprise Manager, I am getting an error message that
states I need to use "With Move" to identify a valid location for the restor
e
file.
On the Restore Database Dialog I enter the name of the mdf file (which is
not in the dropdown list) in the "Restore as database" textbox. Then I
select the device - "Restore from" Disk, click on Add - select the bak file
,
click OK from that dialog, goto the Options tab and enter the physical path
in the "Move to physical file name" window. Then click OK on the Restore
Database dialog and get the error message above - "use With Move..." How do
I use "With Move"?
I also copied the actual mdf file from the production server and moved it to
the Dev server and tried to attach it to the Dev serve. But the mdf is
trying to reference the old Log file. I thought (hoped) it would create its
own log file. Is there a way to get around this?
Thanks,
Rich> I also copied the actual mdf file from the production server and moved it tod">
> the Dev server and tried to attach it to the Dev serve. But the mdf is
> trying to reference the old Log file. I thought (hoped) it would create i
ts
> own log file. Is there a way to get around this?
>
Did you detach the database first, before copying the mdf file?
If not, try detaching the DB, then copying the file(s), then re-attaching to
the other server.|||The file is a replication DB and I did not want to fiddle with
sp_removereplication. Otherwise I would have used the Copy Database wizard.
But since it is in Replication mode, I can't detach it or use the Copy
Database wizard.
What I did was to stop the production server for a few minutes, copied the
file to the dev server - restart the prod server. I did not copy the ldf
because it was kind of large. I was hoping the copy DB would create its own
log file on the Dev server. Is there a way to force that to happen?
Or am I stuck with using sp_removeReplication and then doing the copy?
"Mark Williams" wrote:
> Did you detach the database first, before copying the mdf file?
> If not, try detaching the DB, then copying the file(s), then re-attaching
to
> the other server.|||You will need the ldf file along with your mdf file. What you did is the
same as detach method except you shut MS SQL server down for a few minutes.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:625F97FE-6DC8-4DB3-95AE-392A7CCAB396@.microsoft.com...
> The file is a replication DB and I did not want to fiddle with
> sp_removereplication. Otherwise I would have used the Copy Database
> wizard.
> But since it is in Replication mode, I can't detach it or use the Copy
> Database wizard.
> What I did was to stop the production server for a few minutes, copied the
> file to the dev server - restart the prod server. I did not copy the ldf
> because it was kind of large. I was hoping the copy DB would create its
> own
> log file on the Dev server. Is there a way to force that to happen?
> Or am I stuck with using sp_removeReplication and then doing the copy?
> "Mark Williams" wrote:
>|||Yeah, I figured I would have to do that. the ldf is like 3 gigs +. So once
I get the DB attached, what is the best way to shrink down the ldf? Can I
delete it once the mdf is attached and it will create a new one?
"Grant" wrote:
> You will need the ldf file along with your mdf file. What you did is the
> same as detach method except you shut MS SQL server down for a few minutes
.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:625F97FE-6DC8-4DB3-95AE-392A7CCAB396@.microsoft.com...
>
>|||> I get the DB attached, what is the best way to shrink down the ldf?
You shrink if *before* you detach. Or after you have attached. Use DBCC SHRI
NKFILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D1264111-609E-4190-922C-A88BB233D0CB@.microsoft.com...
> Yeah, I figured I would have to do that. the ldf is like 3 gigs +. So on
ce
> I get the DB attached, what is the best way to shrink down the ldf? Can I
> delete it once the mdf is attached and it will create a new one?
> "Grant" wrote:
>|||Following or explaining GUI in text is incredibly hard. I suggest you invest
igate the file structure
of your backup using RESTORE FILELISTONLY. And based on that construct a RES
TORE command with the
proper commands. If restore from that doesn't work, you can post the output
from RESTORE
FILELISTONLY, your RESTORE command you tried and the error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
> Hello,
> I copied a backup file of a production DB on a production server to anothe
r
> location on a Development server. When I try to restore this backup on th
e
> Dev server - from Enterprise Manager, I am getting an error message that
> states I need to use "With Move" to identify a valid location for the rest
ore
> file.
> On the Restore Database Dialog I enter the name of the mdf file (which is
> not in the dropdown list) in the "Restore as database" textbox. Then I
> select the device - "Restore from" Disk, click on Add - select the bak fi
le,
> click OK from that dialog, goto the Options tab and enter the physical pa
th
> in the "Move to physical file name" window. Then click OK on the Restore
> Database dialog and get the error message above - "use With Move..." How
do
> I use "With Move"?
> I also copied the actual mdf file from the production server and moved it
to
> the Dev server and tried to attach it to the Dev serve. But the mdf is
> trying to reference the old Log file. I thought (hoped) it would create i
ts
> own log file. Is there a way to get around this?
> Thanks,
> Rich|||Thank you for your suggestion. Here is what Restore Filelist returned:
Subscriber_Data D:\MSSQLDATA\Subscriber_
Data.MDF D PRIMARY 654573568 3518437
2080640
Subscriber_Log E:\MSSQLLOGS\Subscriber_l
og.LDF L NULL 2818572288 35184372080
640
And here is what I tried with Restore Database - which is currently running
RESTORE DATABASE Subscriber
FROM DISK = 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber.bak'
WITH MOVE 'Subscriber' TO
'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Data.mdf',
MOVE 'Subscriber_log' TO
'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Log.ldf'
I will post the results of this restore effort - still running - probably
for an hour or so.
"Tibor Karaszi" wrote:
> Following or explaining GUI in text is incredibly hard. I suggest you inve
stigate the file structure
> of your backup using RESTORE FILELISTONLY. And based on that construct a R
ESTORE command with the
> proper commands. If restore from that doesn't work, you can post the outpu
t from RESTORE
> FILELISTONLY, your RESTORE command you tried and the error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
>|||Success!!! Thank you very much for your help. The restore DB works
perfectly!
"Tibor Karaszi" wrote:
> Following or explaining GUI in text is incredibly hard. I suggest you inve
stigate the file structure
> of your backup using RESTORE FILELISTONLY. And based on that construct a R
ESTORE command with the
> proper commands. If restore from that doesn't work, you can post the outpu
t from RESTORE
> FILELISTONLY, your RESTORE command you tried and the error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
>|||The restore command looks fine with one comment, and I also noticed the othe
r post where you said it
was successful.
The comment is that you specified the location for the database files to be
created by your restore
process to be in the Backup directory. Not that it is harmful, but perhaps a
little bit unusual.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C2E59AEB-3FF8-4930-B0E6-DDACEE2B9104@.microsoft.com...
> Thank you for your suggestion. Here is what Restore Filelist returned:
> Subscriber_Data D:\MSSQLDATA\Subscriber_Data.MDF D PRIMARY 654573568 35184
372080640
> Subscriber_Log E:\MSSQLLOGS\Subscriber_log.LDF L NULL 2818572288 351843720
80640
>
> And here is what I tried with Restore Database - which is currently runnin
g
> RESTORE DATABASE Subscriber
> FROM DISK = 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber.bak'
> WITH MOVE 'Subscriber' TO
> 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Data.mdf',
> MOVE 'Subscriber_log' TO
> 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Log.ldf'
> I will post the results of this restore effort - still running - probably
> for an hour or so.
>
> "Tibor Karaszi" wrote:
>sql
Showing posts with label production. Show all posts
Showing posts with label production. Show all posts
Wednesday, March 28, 2012
Monday, March 12, 2012
Problem Moving and Restoring a Large database
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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >
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
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >
Problem Moving and Restoring a Large database
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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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:
[vbcol=seagreen]
> 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:
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...
>
>
|||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...[vbcol=seagreen]
> 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:
|||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...
>
>
|||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:
[vbcol=seagreen]
> 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:
Problem Moving and Restoring a Large database
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 nod
e
> active passive cluster (I know very little about the network side of thing
s,
> 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 offic
e
> 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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||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 becau
se
> the method he is using now is to compress the .bak file using winrar and h
e
> expects that to take 13 hours total to compress the database and chop it u
p
> into 100MB chunks using winrar. He has paused this process until off hour
s
> 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 see
m
> quite right. I requested this be done over 2 weeks ago, and the request h
as
> 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...
>
>|||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:
[vbcol=seagreen]
> 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 ca
n
> 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 fi
le.
> 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 an
d
> 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:
>
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 nod
e
> active passive cluster (I know very little about the network side of thing
s,
> 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 offic
e
> 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...
>
>|||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...[vbcol=seagreen]
> 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:
>|||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 becau
se
> the method he is using now is to compress the .bak file using winrar and h
e
> expects that to take 13 hours total to compress the database and chop it u
p
> into 100MB chunks using winrar. He has paused this process until off hour
s
> 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 see
m
> quite right. I requested this be done over 2 weeks ago, and the request h
as
> 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...
>
>|||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:
[vbcol=seagreen]
> 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 ca
n
> 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 fi
le.
> 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 an
d
> 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:
>
Subscribe to:
Posts (Atom)