14

I have a Window Server 2000 machine running MS SQL Server that stores over 20GB of data. The database is backed-up every day to the second harddrive. I want to transfer those backup files to another computer to build another test server and for recovery practicing. (the backup never actually got restored for almost 5 years. Don't tell my boss about that!)

I have trouble transfering that huge file through the network. I've tried plain network copy, apache download, and ftp. Any method I tried end up failing when the amount of data transfered reach 2GB. The last time that I successfully transfered the file, it was through a usb attached external harddrive. But I want to perform this task routinely and preferably automatically.

Wonder what is the most pragmatic approach for this situation ?

Improve this question
4
  • What file system are you using on the disk that you are transferring to?
    – Marko Carter
    May 29, 2009 at 9:46
  • NTFS. Is that matter ?
    – Sake
    May 29, 2009 at 9:52
  • It matters because the target filesystem could have had a 2GB file size limit that could have caused your error always at 2GB. But it's NTFS so that's probably not it :)
    – Lucas
    May 29, 2009 at 13:35
  • Yeah, I bet money that it's not actually NTFS.
    – Brent Ozar
    May 29, 2009 at 15:16

19 Answers 19

Reset to default
16

A failure predictable at 2Gb sounds like the target filesystem is to blame... Are both on NTFS? Are you piping through any compression (zip used to fail at 2gb boundaries) ((is apache doing compression))

I have copied many files over 20Gb using robocopy (as others have mentioned) but I'd avoid using the /MIR switch until you are sure you have got the copy doing what you want - since it will delete files as well as copy them.

SMB suffers from a one packet at a time limit so is often the slower way to copy files - you have the option to copy using push or pull. Personally, I prefer the push method (copy is initiated by the source).

Improve this answer
2
  • 3
    Totally agree. 2gb is a common bottleneck for FAT filesystems. I'd really check closely to make sure you're not trying to copy to a FAT filesystem.
    – Brent Ozar
    May 29, 2009 at 16:20
  • i thought that was 4 gb?
    – Journeyman Geek
    Mar 19, 2010 at 21:26
10

The MS Exchange tool eseutil is an excellent utility to copy large files quickly across a network:

eseutil /y source_file /d dest_file.

Improve this answer
3
  • +1 Never thought to use that for anything other than Exchange! I'll have to give that a whirl.
    – squillman
    May 29, 2009 at 16:59
  • 3
    From technet.microsoft.com/en-us/library/aa998673(EXCHG.80).aspx "The Exchange Server Database Utilities (Eseutil.exe) /Y copy file mode is optimized to copy very large files efficiently. You can use the /Y switch to copy a database file or log file. However, the mode is not suitable as a general purpose copy utility"
    – Goyuix
    Dec 31, 2009 at 15:38
  • +1, this is probably the most awesome side effect of a database maintenance utility ever heard of!
    – Massimo
    Feb 9, 2010 at 20:43
6

I highly recommend using the free utility RichCopy. It is multithreaded and can pause and resume file copy operations. I have had very good luck using it to transfer files between severs.

My three top tips for using RichCopy

  1. If you are copying one or a few big files, set ‘File Copy’ attribute to more than '1’. It uses up resources but copies big files down quicker

  2. If you are copying lots of files then set the ‘Thread number’ attributes to 10-10-1. This will copy multiple files quicker

  3. If you are copying over a dodgy connection. You can re run the download and it will go and find the files it didn't manage to get the first time.

http://blogs.technet.com/markdea/archive/2009/03/24/richcopy-is-it-the-new-sliced-bread.aspx

Improve this answer
5

As far as file copy utilities go, TeraCopy is a nice GUI-based one (not command line) that can queue lots of files, supports pausing and resuming, can dynamically change its buffer size to optimize speed, and can optionally replace Windows Explorer's default copy/move with its own.

Improve this answer
1
  • note: free version not allowed to run on servers. bummer.
    – tinker
    Jun 10, 2020 at 3:41
3

Robocopy with the /MIR option is very useful for quick and dirty backups between machines. You can find robocopy in the Windows Server 200X Resouce Kit

MIR will MIRror the contents of one directory to another server. It will only copy files that have changed.

Improve this answer
1
  • 2
    /Z is also a sweet option. It lets you resume failed copies. This has saved my life over slow networks
    – Nick Kavadias
    May 29, 2009 at 16:30
2

The most pragmatic solution to repeated shuffles of large SQL Server backup files is to use a third party backup compression product or SQL Server 2008 Enterprise Edition's built-in backup compression.

There's several out there from different vendors. I work for Quest Software, the makers of LiteSpeed, but I'm not here to sell anything. You want to check out all of the products out there and decide what's best for your needs. Here's a recent blog post talking about LiteSpeed specifically, but the same concepts apply to other products too:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-8

Improve this answer
1

Are you copying the file over a LAN or through some WAN connection like ADSL? I assume it's a WAN because 20GB is not a big file to copy over a LAN. I copy many such files every day.

If it's a WAN connection then the way I do it is to use the Cygwin version of rsync.

JR

Improve this answer
1

I had network transfers fail at around the 2GB mark - it turned out to be a faulty NIC.

Improve this answer
3
  • 3
    faulty NIC always failed around 2GB? wierd!
    – Lucas
    May 29, 2009 at 13:42
  • maybe this NIC had hardware accelerated TCP with a bug in it?
    – qbeuek
    May 29, 2009 at 16:35
  • I'm not 100% sure what was wrong with the NIC itself, but it was an unbranded affair from eBay - as soon as I replaced it, the network transfers improved greatly with no drop in connectivity.
    – Lazlow
    May 29, 2009 at 22:38
1

I use syncback ( http://www.2brightsparks.com/syncback/sbse.html ) daily for transferring files several times larger than yours. Never had a problem with it.

Improve this answer
1

Its a bit late but I would recommend a 3rd party backup and restore application option. We use Red Gate SQL Backup (www.red-gate.com), it has compression and alternate location options in the GUI. I get compressions saving of 80% average - so you only transfer 20% of the actual db size. It also supports encryption so it can be used over a WAN without interception worries.

Its fully schedulable so can run automatically at a cycle of your choosing.

The GUI also allows you to configure and administer log shipping.

Free trial version available at the above.

Improve this answer
0

I don't have experience with such a large file, but could you use robocopy or even xcopy with the /Z option that claims to be restartable. It seems like this is intended for large file copies where the network is not reliable.

Improve this answer
0

I've used robocopy to well over 1gb and had no problems. ss64.com has a good explanation of the switches. I can't post the link though :-(

Improve this answer
0

Evil answer..

Use Netcat. A unix oriented tutorial for transferring files can be found here. You can further speed things up by:

  1. Compress on the sender side and decompress on the destination side. (chuck windows equivalent of gzip in the middle of the command lines.)
  2. Choose to send data via udp instead of by tcp (hey man, who cares about data integrity??!!)

Joking aside, netcat is likely the quickest way to transfer large files on a LAN. As no checksum is done you may wish to do a MD5 sum of the file before sending it and compare it to the MD5 sum of the received file.

I have used netcat a lot in this way, never seen it fail, never seen it fail to max out the network either..

Improve this answer
3
  • Maybe Netcat is easy and quick to use, but I hadn't obtained any high speeds with it. IIRC it can barely reach 2-3 MB/s. When it comes to speed it sucks too much.
    – Cristian Ciupitu
    May 29, 2009 at 15:24
  • I feel some performance tests coming on... I will see if I can find the time to compare nfs,cifs,ftp and nc on my local home network.
    – Matthew
    May 30, 2009 at 9:35
  • If netcat is only giving you 2-3MB/s your system is broken. ftp and netcat should both perform identically. nfs and cifs are also usually about the same.
    – Justin
    Feb 10, 2010 at 0:47
0

Might be worth splitting up the file into smaller chunks as a short term solution until you can pinpoint the problem. We have had problems similar to this in the past, and ftp has always worked for us

Improve this answer
0

If these are SQL .bak files you're copying, I would highly suggest doing one of the following to shrink your files down prior to copying:

  • Shrink the database and truncate the log prior to running the backup. OR
  • Compress the .bak file prior to copying. SQL .bak files compress way down if you're not using the fully allocated space in the data and log files.

Might eliminate the need for an alternative method for copying large files.

Improve this answer
2
  • I've tried that. Not much help unfortunately.
    – Sake
    May 29, 2009 at 14:33
  • Just out of curiosity, then... Do you have your autogrowth set to a hard byte value or a %? Or do you even have it set to autogrow?
    – squillman
    May 29, 2009 at 14:38
0

I don't think finding something to transfer faster is your problem, double check to make sure your target file system is NOT FAT, as others have said. Also, make sure the NICs on both sides have updated drivers and aren't otherwise acting flaky.

With that said, are you moving many many small files or just a few large ones? I've seen RAID controller problems when trying to move millions of tiny files.

I don't think you'll have a problem automating this once you figure out what is causing the failure. It may help to list more details about your hardware and any relevant errors you may see in event viewer.

Improve this answer
0

Have you tried to use an eSATA connection to an external hard drive? Connections are smokin' fast (3 gigabit!) and should be able to transfer that file in no time!

What speed is your network card on the server, 10/100 or 10/100/1000? What does the server network bandwidth and switch look like when you are coping the file? What does the destination location's (server's?) network bandwidth look like when copying? Have you tried to teamed 2 NIC's together? Are the network card drivers up to date? Is the BIOS up to date?

There are many things that could be the issue for file transfers. Making sure the hardware drivers and BIOS are up to date can really make a difference.

-JFV

Improve this answer
0

The simplest and fastest way: external USB disks and walking.

My way: use rsync. If the copy fails, just restart it, and it will pick up where it left.

Improve this answer
0

The other thing to check would be to see if the quota service is set up on the target server; I think it uses 2 GB as the default quota per user.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .