I have a dedicated Windows Server in a remote data center, where my site is running along with a Microsoft SQL Server database. The database is is 50+ GB and I want to have a local backup of the database. The problem is how to transfer a huge backup file from the remote server. I have tried regular FTP clients (like Filezilla) and also WinSCP, but both are having trouble with huge files. Is there any other alternative that would be reliable in this case. I'm planning to automate the process and download the backups every weekend.
1Have you thought of using SQL server's built in DR functions (there are a number of options) to handle this? You'd need another SQL server at the second-site but it works very well and actually gives you an instantly usable DB if the worst happens.– Chopper3Jul 16, 2012 at 10:13
Also, try to compress that sql file either using zip or using sql's native backup compression. This should usually save you 50%+ of file size– MichelZJul 16, 2012 at 10:52
1A workaround , you split the file into smaller chunks. winzip has a function for this, you can split the file in many <4GB chunks. Many file utils have trouble with files bigger than 4GB because the are work with 32bit limitations. winscp.net/eng/docs/faq_4gb , try using SFTP which may get around the 4GB limitation.– The Unix JanitorJul 16, 2012 at 11:05
2After copying 560gb the last week and having 4000gb coming in the next 2 weeks - what exactly is your problem?– TomTomJul 17, 2012 at 16:45
If you haven't tried this already, you can use SQL management studio to do scheduled backups of the database files. I've never tried such a large file to a remote site, but I've had it handle remote sites and much larger files than 50 GB fine, on separate jobs.
Or, failing that, have you tried robocopy? Robocopy should work "fine," is easily scriptable, and while you're scripting the job up in VB or Powershell (or whatever), you may as well throw in some email notifications for success and failure.
Or for that matter, DFS? (You don't say what version of Windows, but DFS is much improved under 2003 and 2008.) I'd honestly be tempted to put up a DFS share for the sole purpose of transferring differential changes of the database from the server to a remote backup location. Transferring a few hundred MB worth of changes a day, in the background with BITS seems like a much better solution than moving the whole 50 GB file every time you want to get a copy.
EDIT: I didn't say it explicitly earlier, though after a bit of thought, I think maybe I should... you absolutely DO NOT want to create a DFS namespace in SQL's working folder, where the database file is currently located. If you go the DFS route, you'd want to do a local copy/backup of the database file and share that location over DFS. (And set the backup/copy job to over write the existing file, or DFS would end up replicating the whole file every time, rather than just the changes.)
As a bonus, all these options should be available to you within Windows, rather than relying on third-party tools.
What trouble are you having with FTP and WinSCP?
If the transfer is failing around the 4GB mark, make sure you are transfering to an NTFS filesystem. Other filesystems have limits on file size.
If if's failing because the network connection is cutting out, you can setup either option to resume quite easily.
You don't mention what kind of internet connection you have locally. But if we assume a 5mbit cable or DSL connection, it should take a little over a full day to transfer, so it is possible to do weekly backups like this.
Another way would be to setup a local SQL server and setup replication. The initial sync will take quite awhile, but after that you should get fairly up to date syncing happen automatically.