1

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
6
  • 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, 2010 at 18:15
  • When you say installing an SSH server is not an option, does that apply to both ends or just the remote? May 5, 2010 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, 2010 at 13:06
  • is H: a local disk?
    – tony roth
    May 10, 2011 at 16:05
  • and nothing in the eventlog that corresponds to this
    – tony roth
    May 10, 2011 at 16:40

8 Answers 8

2

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.

1

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.

1
  • 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, 2010 at 13:40
1

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

1
  • 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, 2010 at 13:01
1

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.

0

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.

1
  • 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, 2010 at 13:17
0

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.

1
  • 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, 2010 at 13:21
0

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.

1
  • 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, 2010 at 16:15
0

I'm testing this right now:

  1. SQL Dump to .bak files
  2. GZip with the rsyncable option
  3. rsync the gzip files.

RSync is probably the best even if you don't gzip it because it only dups the diff.

I'm using cygwin binaries and my batch has these helpful lines (as well as many others including emailing me the log):

:: Getting a list of backup files ".bak"
dir /b c:\temp\db_backups | findstr bak > c:\temp\db_backups\filelist.txt
:: For each line in the file list from above, gzip it rsyncable!
for /F %%i IN (c:\temp\db_backups\filelist.txt) DO c:\cygwin\bin\gzip -9 --rsyncable -v -q c:\temp\db_backups\%%i

:: rsync it to a UNC path on a server
:: if you want to ssh it or ftp it you'd need to tweak the transport method
:: but I'm just using a UNC path.

c:\cygwin\bin\rsync --remove-source-files -Prv --delete --force --stats --itemize-changes --log-file=c:\temp\daily.backup.rsync.log "/cygdrive/c/temp/db_backups" "//unc/path/to/my/server/e$/dbs"

Usually you represent UNC paths as \unc\path\to\my\server...etc but cygwin and rsync no likey the other way.

You can also use rsync over ssh.

rsync -PavzrH -e "ssh" --delete --force --stats --itemize-changes --log-file=/cygdrive/c/temp/rsync_offsite_bkp.log /cygdrive/c/temp/db_backups [email protected]:/mnt/data/share/stuff

the "/mnt/data/share/stuff" is the target directory where all your stuff gets stored on the ssh server.

I will ALSO WARN YOU that if you're using cygwin (highly recommended) you also need in your cygwin home directory a ".ssh" folder and to create a:

"config" "id_rsa"

Files containing your config:

Host yoursshserverhost.com
  HostName yoursshserverhost.com
  Port 22
  User your_user
  IdentityFile ~/.ssh/id_rsa
  AddressFamily inet
  BatchMode yes
  ForwardAgent yes
  ClearAllForwardings yes

The id_rsa is your "key" you need to export from your ssh server. I don't have that handy at the moment but there is information online how to make an ssh key. Once you made it on your server you cat it and you can copy and past it into another "id_rsa" file on your server. You can also name it whatever you want "my_special_super_secret_key" as long as you point to it in your config in the .ssh folder in your cygwin home directory.

So:

  1. sql dump
  2. gzip with rsyncable option
  3. rsync over ssh for secure backup / file copy diffs

RW

You must log in to answer this question.

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