March 15, 2004 at 3:08 am
I want to tranfer SQL server 2000 database which is about 7 GB, to another location which is about 100 KM away.
Currently I am using DAT drives, but it has some compatibilities issues, sometime it works fine but most of the time it fails and we need to write it again and send it across.
Can anyone suggest anyother way you can transfer without problems?
March 15, 2004 at 8:34 pm
Removal IDE hard drive would be fast and reliable.
Arden
March 16, 2004 at 1:35 am
Another alternative would be to compress the database dump or detached mdf/ldf using something like WINRAR or WINACE. This may get the archive file down to less than 700mb - the size that can be burnt to a CD. Or use the splitting capabilities in WINRAR to create multiple archives so that it can be burnt across several CDs. This assumes you have a CDR or CDRW drive available at the original location. HOwever compressing a 7Gb file takes time, so it depends how often you need to transfer the files.
Another alternative, again using CDR(W)s as the transport medium, would be to do a database dump with multiple devices, then burn each dump device to a CD.
backup database [MYDB]
to disk = 'MYDB_Dump_Part1.bak'
, disk = 'MYDB_Dump_Part2.bak'
, disk = 'MYDB_Dump_Part3.bak'
, disk = 'MYDB_Dump_Part4.bak'
, disk = 'MYDB_Dump_Part5.bak'
for as many devices as necessary so each is less than 700Mb.
When at the destination site, copy them to the local machine HD, and use equilavent RESTORE command listing all the devices.
March 16, 2004 at 8:52 am
I've compressed a 20 G database to about 4 using WINRAR, the I was able to copy it to a DVD and mail it. There also exist several Large File Transfer Services if you have to do this often. They transfer files over the Internet.
Francis
March 16, 2004 at 2:32 pm
Do yourself a favor: be sure to shrink the files before you dump/detach. I've had good luck with WinRAR. WinZIP has problems (or at least it did when I needed to do something similar) with large files. The compression rate depends on the content but I've had most dumps shrink down to about 10% of their original size. You might get away with one CDs worth of .RAR file if you use the best compression.
Good luck.
Ken
March 16, 2004 at 9:59 pm
This transfer is on a daily basis. Will "log shipping" or replication help ?
March 17, 2004 at 7:47 am
Log shipping may be a good alternative.
Francis
March 17, 2004 at 8:33 am
WINRAR - is a good option. You could break the winrar into 50MB chunks and then FTP then over. I have transfered 40GB DB's like that to remote locations and it works great. If you need the data refreshed often, then log shipping will be a good option. SQL Server log shipping might not work as you need to ship to a different location. I have created my own scripts to do that, that is backup the translog once an hr ftp it to the standby DB server, which is a ready-only DB and then apply the translog on that server through another BAT script and then archive the trans log from it to be backed up to tape.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply