Full Version of this article can be found here

MySQL Automated offsite backup

With a site like this one any data loss is VERY bad, so I've done quite a bit of work on automating my backup systems. The first step is of course created a rolling backup system on the server itself. I wrote "MySQL DB backup - Automated shell script" to cover that side of things. The next step is often overlooked. An off server backup is also just as important as the backup on the server itself. What happens if the hard drive straight up dies? Those backup files on the server won't help you at ALL!

This next step is to help show you what I've done to have my windows system automatically grab one of those backup files from the server to have what is known as an "offsite backup" Alot of other peoples instructions are to put FTP commands into that shell script which is run on your server, but how many of us have multiple dedicated servers? Not me! (yet) My solution to this problem is to take advantage of the FTP commands built into windows!

Step 1: DB Dumps

I just got done writing a guide to automated DB dumps here. You'll want to use that if you don't already have a system setup to automate your DB dumps. You'll also want to make sure that they're stored in a directory that you can FTP into. Otherwise you won't be able to follow this guide :(

Step 2: Create files:

backup.bat
ftp.cmd

Step 3: backup.bat

right click on backup.bat and select edit. Replace E:\ftp.cmd with wherever you've placed the file. Insert the following into the file:

ftp -s:e:\ftp.cmd

Step 4: ftp.cmd

Right click on ftp.cmd and select edit. Insert the following (bold means customize):

open SERVER
USER
PASS
cd LOCATION
get FILE.sql.gz

Step 5: Customize

SERVER will either be an IP or the domain name of the FTP address you're connecting to.
USER is the username needed to log into the FTP
PASS is the password required to log into the FTP
LOCATION may or may not be needed depending on the default path when you log in. Most people will need to change to the appropriate directory.
FILE is of course the name of the file you'd like to automatically download.

Step 6: Schedule It

Start --> Settings --> Control Panel --> Scheduled Tasks --> Add new task

Simply follow the wizard and you're done! On a site as massive as this one I only do weekly backups to my office system with daily backups on the server.