Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I've been having a hard time copying large database backups from the database server to a test box at another site. I'm open to any ideas that would help me get this database moved without having to resort to a USB hard drive and the mail.

The database server is running Windows Server 2003 R2 Enterprise, 16 GB of RAM and two quad-core 3.0 GHz Xeon X5450s. Files are SQL Server 2005 backup files between 100 GB and 250 GB.

The pipe is not the fastest and SQL Server backup files typically compress down to 10-40% of the original, so it made sense to me to compress the files first. I've tried a number of methods, including:

  • gzip 1.2.4 (UnxUtils) and 1.3.12 (GnuWin)
  • bzip2 1.0.1 (UnxUtils) and 1.0.5 (Cygwin)
  • WinRAR 3.90
  • 7-Zip 4.65 (7za.exe)

I've attempted to use WinRAR and 7-Zip options for splitting into multiple segments. 7za.exe has worked well for me for database backups on another server, which has ~50 GB backups.

I've also tried splitting the .BAK file first with various utilities and compressing the resulting segments. No joy with that approach either- no matter the tool I've tried, it ends up butting against the size of the file.

Especially frustrating is that I've transferred files of similar size on Unix boxes without problems using rsync+ssh. Installing an SSH server is not an option for the situation I'm in, unfortunately.

For example, this is how 7-Zip dies:

H:\dbatmp>7za.exe a -t7z -v250m -mx3 h:\dbatmp\zip\db-20100419_1228.7z h:\dbatmp\db-20100419_1228.bak

7-Zip (A) 4.65  Copyright (c) 1999-2009 Igor Pavlov  2009-02-03
Scanning

Creating archive h:\dbatmp\zip\db-20100419_1228.7z

Compressing  db-20100419_1228.bak

System error:
Unspecified error
share|improve this question
    
What exactly is the error or issues you're seeing? Yes, I see the 7z example above but you mention that other tools have failed as well. Is it more like the "can't copy big files in windows 2003" type issues? Are you seeing memory usage going up when copying? –  Chris_K May 5 '10 at 18:15
    
When you say installing an SSH server is not an option, does that apply to both ends or just the remote? –  John Gardeniers May 5 '10 at 22:09
    
@Chris_K: No, that's the thing- memory usage is relatively tame. It depends on the tool, but they use between 250 MB and 1 GB. Lots of RAM left on this particular box. @John: Unfortunately, not an option on both ends. I could push for it on the remote, but it'd be a hard sell. –  Aaron May 9 '10 at 13:06
    
is H: a local disk? –  tony roth May 10 '11 at 16:05
    
and nothing in the eventlog that corresponds to this –  tony roth May 10 '11 at 16:40

7 Answers 7

Could you use rsync, without the ssh? I don't see where encryption is a requirement, and rsync runs okay on Windows.

How about gzip through netcat? The file(s) should just go through, without running into the problems of the intermediate steps.

Just thinking out loud...sorry I don't have something definitive.

share|improve this answer
    
No encryption is fine with me. Honestly, I haven't tried rsync yet for this particular problem. I haven't had the best luck using rsync over SMB shares- in my experience it's been quite a bit slower and less robust than xcopy or robocopy. I'll give it a try though- at this point, the only option I have is physically shipping drives around, so I'm willing to try anything! Hadn't thought of netcat plus gzip- it's an idea worth exploring at least. No need to apologize, you've provided the best ideas so far! :) –  Aaron May 9 '10 at 13:40

After having compressed the db, could you consider using robocopy to copy over the file?

share|improve this answer
    
+1, 7zip + RoboCopy –  Chris S May 7 '10 at 19:27
2  
With all due respect, did you even read the question? I haven't been able to compress with 7zip, or any tool yet. See my question for a list of the compression tools I have atried. Compressing and copying with robocopy is usually what I do- I'd love to do it now- but 7zip fails during the compression. –  Aaron May 9 '10 at 13:01

qpress for fast compression with low cpu load, i used it for 130GB SQL 2008 backup dump. It compressed to 34GB file at ~35MiB/s which I think is impressive.

share|improve this answer

If your utilities are choking on the size of the backup file, just make more backup files, each will be smaller. You can have the SQL Server backup command to use multiple files. Microsoft calls this a "striped" backup set. Here's a stupid example:

backup database foo to disk='c:\foo.01.bak', 'c:\foo.02.bak', 'c:\foo.03.bak'

You can specify as many files as you would care to. Full documentation on the backup command is here.

Alternatively, if you have some money to burn you can use CA's Lightspeed or a similar product. This is an install on the server, but it will compress all server backups. This will result in shorter backup times, which DBAs and sysadmins usually like. CA provides a distibutable command-line utility to decompress a backup that uses their format, so you can send the backup file to anywhere.

share|improve this answer

Well here e is what I did ;)

  • Virtualization on both ends.

  • Domain controller on both ends ;) Gives me an external DC backup.

  • DFS replication for transfers ;) This one runs nicely in the background, uses compression and just keeps ticking.

share|improve this answer
    
Good to hear how other folks do it, but I'd be hard pressed to get approvals for changes that big. :P Thanks! –  Aaron May 9 '10 at 13:17

When I need to bring our database down from the data centre to the office I just do the backup in MSSQL and then compress again with winrar. I bring a 4 gig DB backup down to about 500MB then I just use the FTP server to do the transfer. Even if it stops FileZilla will let you resume.

Takes about 15 minutes to download.

share|improve this answer
    
Please read my question. That's what I typically do, but winrar, 7zip, gzip, etc have all failed me. I wish they were just 4 GB- I'd do just that. That strategy has worked fine for 50 GB backup files, but these are much larger, 100-250 GB. Big enough to that 7zip and WinRAR choke. –  Aaron May 9 '10 at 13:21

Try compressing using IZARC http://www.izarc.org/izarccl.html - which has a command line addon. I had cases when compressing was not succesfull on large files (about 25GB in size) but managed using IZARC -although I used the gui version not the command line addon.

share|improve this answer
    
I'll take a look- thanks! I prefer commandline, but I can deal with a GUI- I don't need to automate these jobs, just an occasional ad-hoc thing I need to do. –  Aaron May 10 '10 at 16:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.