Backup Windows Over SCP to Linux

Question

I have a need to regularly backup some files from a Windows box both locally (on a different drive) and to a remote location over the Internet. I'm thinking of using 7zip to create the incremental backups on the local machine, and then scripting WinSCP to send them to a remote box running an OpenSSH server that will (probably via a cron job) take care of dating the backups and storing them away in neat folders.

With the links provided above, I think I can glue this together, but my question is whether or not this is a "good" solution. Is there anything I'm overlooking with this method? Is there a better (simpler?) way for me to go about doing this (note: the project has 0 budget, so while I'm a huge fan of Backblaze, it's out this time)? Is there any undue security concerns I'm ignoring? I guess I'm looking for a "best-practices" kind of thing here.

Thanks!

Answer 1

7zip + pscp [ coming from putty ] + private key without pass-phrase will work fine from windows. i do not know what is your data size, how often it changes and so on; it's not best practice but here is what i use:

• vshadow from microsoft's volume shadow copy to create snapshot of windows file system [ and avoid problem with opened / locked files ]
• SHADOWRSYNC script with tiny modification - i've replaced rsync with..
• .. rdiff-backup to handle differential backup
• plink from putty to allow secure transport layer for rdiff-backup over ssh connection.

this gives me handy, not-bandwidth-hungry way of backing up data with ability to recover n previous versions of changed files.

Answer 2

There's also duplicity if you're interested in rsync + gnupg encryption + ssh / s3 / various other storage back-ends. There's a little leg-work involved in making it work on Windows (namely installing Cygwin and the requisite supporting software packages), but that's not too hard.

You'll have the benefit of encrypted, differential backups which you can store on a Linux (or other SSH-enabled server), Amazon's S3, etc.

Answer 3

I second rsync + ssh, that's what i'm using on my own network

Consider also rsnapshot for backups

Answer 4

really like this solution. Thank you. I've never played with the shadow copy stuff before, but it looks super useful. This will let me back up MSSQL databases without stopping the service, right?

No, absolutely not. You cannot naively back up an open database because its various component files will be out of sync and you will miss the in-memory stuff. You must either shut it down or use one of Sql Server's native backup methods or a proprietary solution.

Answer 5

(Copied over from an article I just put on Stack Overflow)

The folks at MSSQLTips have some very helpful articles, the one most relevant for this is "Automating SQL Server 2005 Express Backups and Deletion of Older Backup Files"

The basic approach is to set up two tasks using the Windows Task Scheduler. One task runs a TSQL script that generates separate backup files for all MSSQL databases (except TEMPDB) with the database name and a date/time stamp in the file name into a specified directory. The second task runs a VBScript script that goes through that directory and deletes all files with a .BAK extension that are more than 3 days old.

Both scripts require minor editing for your environment (paths, how long to keep those database dumps) but are very close to drop-in-and-run.

Note that there are possible security implications if you're sloppy with these or with directory permissions, since they are plain text files that will need to run with some level of privilege. Don't be sloppy.

I don't know about the requirements for master/slave type setups with MSSQL, but it does support replication and there's documentation out there. Hopefully that same documentation will indicate whether it's possible to do it with MSDE/Express as well as with the full product.

Original answer, revised to down here because my reading comprehension was poor and I read MSSQL as MySQL:

For database backups, look at the MySQL documentation. Among other options, mysqldump or (possibly) mysqlhotcopy will let you dump the contents of the database for backup even while it's in use. From my reading of the documentation, you'd generally not have noticeable interruptions.

One drawback of this approach is that it gives you a new dump file each time, so rsync may not help cut down network traffic.

Another option might be to run Master and Slave MySQL servers - I've not done this myself yet (only one customer doing anything with MySQL), but there's plenty of documentation out there including How To Back Up MySQL Databases Without Interrupting MySQL on HowToForge