In a project I've been working on, I wanted a way to remotely backup a mySQL database on to Amazon S3. Because the site was hosted on a shared server, I also didn't have access to the command line.

There are probably dozens of different ways to go about doing this, but the solution I arrived at is incredibly simple and easily lends itself to a quick restore script.

First, we need to get a list of tables in the database:

<!--- Get data  --->
<cfquery name="qryTables" result="stcTables">
    SHOW TABLES
</cfquery>

We will then get all the data from each of those tables and create a WDDX packet out of each (which is a XML representation of ColdFusion query object). Each of these packets is written in to a text file.

<!--- Pull down data and export each table's data into WDDX file--->
<cfset variables.strTempDirectory = "#expandPath("./")#temp\" />
<cfloop query="qryTables">
    <cfset variables.strTableName = qryTables[listFirst(stcTables.ColumnList)] />
    <cfquery name="qryData">
        SELECT * FROM #variables.strTableName#
    </cfquery>
    <cfwddx
        action="cfml2wddx"
        input="#qryData#"
        output="variables.strData" />
    <cffile
        action="write"
        file="#variables.strTempDirectory##variables.strTableName#.txt"
        output="#variables.strData#"
        charset="utf-8" />
</cfloop>

Finally, we'll take all those text files and zip them into a single file, which will be sent to Amazon S3 (thanks to the CF 9.01 enhancement).

<!--- Package WDDX files into a ZIP file --->
<cfset variables.strBackupZip = "database-backup-#dateFormat(now(),"yyyy-mm-dd")#.zip" />
<cfzip
    action="zip"
    file="#variables.strTempDirectory##variables.strBackupZip#"
    source="#variables.strTempDirectory#"
    filter="*.txt" />

<!--- Send to Amazon S3 --->
<cffile
    action="copy"
    source="#variables.strTempDirectory##variables.strBackupZip#"
    destination="s3://your-bucket-name/database/#variables.strBackupZip#"/>

I've been using Hostek for a couple of years now, and recently inquired about using the latest version of CF for one of my sites. This was the response I got from their support team:

"We currently have all of our new servers running ColdFusion 9.0.1 if you would like we can move the domain to one of the servers running CF 9.0.1, copy the files/DSNs and the have you test it before pointing domain to new server IP... Please let us know the domain name that needs to be moved and if you would like us to proceed. There will be no downtime during control panel migration or site move."

You hear a lot of hosting horror stories out there, but don't always hear when companies get it right.