How-To: Auto-Backup Your Database Part II: CoreFTP
Date: April 10th, 2012
This instruction set is intended for those using a shared server account with HostGator. If you're not using a HostGator account, or have a different type of account, this may not work exactly as-is. Use with discretion, and remember your server host's support ticket system is here to help!
So, well over a year later, here is part two. In the first part, we got the database to backup to a server folder on a weekly basis. This is fantastic! As you always know you'll have a weekly backup. However, these backups will clutter up your server, and if your server goes down entirely, you're still out of luck. So, we download them to the desktop every so often. This is a tedious process, and we can automate this as well.
For this tutorial, we'll use CoreFTP and Windows. Any FTP client which supports command line should be capable of this, but the code may be different. CoreFTP lets you store site profiles, including saved passwords. This will allow you to run command line FTP without specifying passwords in plain text. In particular, we will create a batch file. This batch file is two lines starting with a quotation mark, though your web browser may wrap the text.
|"C:\Program Files (x86)\CoreFTP\coreftp.exe" -s -OS -site ocdtrekkie -d /mysql/*.sql -p C:\sqlbackups\|
"C:\Program Files (x86)\CoreFTP\coreftp.exe" -s -site ocdtrekkie -purge /mysql/*.sql 8
Examining this batch file, you will see that it will download all SQL files from the directory we set up in Part I. It will skip any backups it already has on the local hard drive, in case there is a date overlap. The second line instructs it to purge any SQL files from that folder that are older than eight days, since we want to keep the most recent SQL backup on the server, just in case.
Now, go to your Control Panel, Administrative Tools, and find Task Scheduler. On older machines it may be called Scheduled Tasks. Create a Task and name it "Download Database Backups". Under the Actions tab, you can create an action, and selecting your batch file as the program to start. Under the Triggers tab, you can create a trigger to run on a schedule. I recommend setting it for weekly, about an hour or two after your server creates the backups. Mind time zones and daylight savings when considering this.
And that's pretty much it. Now, your server will create backups, and your computer will retrieve them. They now clutter up your computer's hard drive. I suggest occasionally moving them all into a 7Z or RAR archive. Weekly backups compress extremely well for most databases, as the data from week to week is mostly the same.