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.


  • what version of windows? – Nick Kavadias Dec 3 '09 at 23:28
  • @Nick Kavadias, I believe there will be a mix of WinXPPro and Server2k3 machines. – mrduclaw Dec 4 '09 at 1:23

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.

  • I 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? Thanks again! – mrduclaw Dec 4 '09 at 0:49
  • Shadow copy is equivalent of pulling the power plug from the server... in a way. I believe that there are better ways of backing up msssql, but if you want to uses volume shadow copy - it should do the trick. – pQd Dec 4 '09 at 7:06

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.

  • I like this. My only concern is installing Cygwin and a bunch of tools on the client's machine. I'm trying to keep my footprint as small as possible. At home though, I might go for this as it looks pretty sweet. Thanks! – mrduclaw Dec 4 '09 at 0:43

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

Consider also rsnapshot for backups

  • I use rsync to back up Windows shares on my LAN to an Ubuntu box. How could I go about easily doing this via ssh tunneling (I assume that's what you meant)? Won't the Windows box need an ssh server or rsync? – mrduclaw Dec 4 '09 at 1:27

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.

  • Great info, thanks! How do back-up solutions that offer this capability do it, then? – mrduclaw Dec 4 '09 at 7:12
  • The backup solutions (eg Netbackup) have "agents" or plugins that run Sql Server commands. But where I work we just have Sql Server back up its live databases to disk (using "maintenance plans") and then use Netbackup to archive that output to tape. Since you cannot restore a database from copies of open database files, you can exclude ldf, ndf and mdf files from your ordinary backups. – ramruma Dec 4 '09 at 16:01
  • 1
    It depends on the level of naivety and the quality of the database. If you use a simple cp or xcopy and/or your DBMS does not support atomicity, durability and consistency (everything in ACID except isolation), you may enter an unknown state. If you use atomic block-level snapshots (like with ZFS, btrfs, ReFS) on reliable storage (storage with sync guarantees) and your DBMS supports ACID, you are fine by definition (excluding bugs of course). In this case restoring such a backup will be no different to a hard power reset (meaning pulling the power cord of the server). – user121391 Jun 21 '16 at 12:27

(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

  • Just being pedantic, is this the same for MSSQL servers? Thanks! – mrduclaw Dec 4 '09 at 9:14
  • Similar, but the details differ. I misread MSSQL as MySQL, so I revised the answer to have details on MSSQL backups. This assumes you're running SQL Server Express rather than the full product - if you're using the full product, the approach I link to duplicates functionality that's already available to you. – fencepost Dec 4 '09 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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