Backup and Restore data with WDDX

July 18, 2008

A while back I saw a cool post on Ray Camden's site about Using CFDBINFO and CFZIP for quick database backups. Recently I wanted to get the data out of a table in a remote MS SQL Server database and restore it to my local development enviroment, so I thought I'd write a script to import the data, that Ray's code backs up. I also added the ability to choose which tables to restore.

To recap this is pretty much what Ray wrote to backup the database.

<cfset datasource="mydsn" />
<cfdbinfo datasource="#datasource#" name="tables" type="tables" />
<cfset data = StructNew() />

<cfloop query="tables">
  <!--- grab all data from table --->
  <cfset data[table_name] = StructNew() />
  <cfdbinfo datasource="#datasource#" table="#table_name#" name="qryTableFields" type="columns" />

  <cfset data[table_name].schema = qryTableFields />

  <cfquery name="getData" datasource="#datasource#">
    SELECT *
    FROM [#table_name#]
  </cfquery>
  
  <cfset data[table_name].data = getData />
</cfloop>

<!--- Now serialize into one ginormous string --->
<cfwddx action="cfml2wddx" input="#data#" output="packet" />

<!--- file to store zip --->
<cfset zfile = expandPath("./data.zip") />

<!--- Now zip this baby up --->
<cfzip action="zip" file="#zfile#" overwrite="true">
  <cfzipparam content="#packet#" entrypath="data.packet.xml" />
</cfzip>

<cfoutput>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Backup Results</title>
<style type="text/css">
body{
  font-family:Verdana, Arial, Helvetica, sans-serif;
  padding:80px;
  text-align:center;
  border-top:20px solid ##006699;
  margin:0;
}
h1{
  color:##006699;
  font-size:18px;
  font-weight:bold;
}

p{
  color:##666666;
  font-size:11px;
}
</style>
</head>

<body>

I retrieved #tables.recordCount# tables from datasource #datasource# and saved it to #zfile#.

</body>
</html>
</cfoutput>

And here is the restore part of it that I wrote (it's not pretty but it works!)

<cfset dsn = "mydsn" />
<cfset bDeleteData = True />
<cfset zfile = expandPath("./data.zip")>

<cfzip action="read" file="#zfile#" entrypath="data.packet.xml" variable="wddxText" />
<cfwddx action="wddx2cfml" input="#wddxText#" output="qImport" />

<cfoutput>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Import Database Tables</title>
<style type="text/css">
body{
  font-family:Verdana, Arial, Helvetica, sans-serif;
  padding:30px 80px 30px 80px;
  text-align:left;
  border-top:20px solid ##006699;
  margin:0;
}
h1{
  color:##006699;
  font-size:16px;
  font-weight:bold;
}
h2{
  color:##006699;
  font-size:12px;
  font-weight:bold;
}
p, label{
  color:##666666;
  font-size:10px;
}
label.sys {
  color:##CCCCCC
}
td {
  width:50%;
  vertical-align:top
}
</style>
</head>

<body>


<h1>Restore Data</h1>

<p>Select the tables to restore</p>

<form action="#CGI.SCRIPT_NAME#" method="post">
<table summary="restore" width="550">
  <tr>
    <td>
    <cfloop list="#ListSort(StructKeyList(qImport), "text")#" index="table">
      <input type="checkbox" name="tablename" id="tablename_#table#" value="#table#" />
      <label for="tablename_#table#" class="#Left(table,3)#">#table#</label><br />
      
    </cfloop>

    </td>
    <td>
      <input type="checkbox" name="delete" id="delete" value="1" />
      <label for="delete">Tick the box to show that you understand that the existing data will be deleted!</label><br />
      <input type="submit" name="submit" value="restore" />
    </td>
  </tr>
</table>
</form>

<cfif StructKeyExists(form, "tablename")>
  <cfif Not StructKeyExists(form, "delete")>
    <p>You didn't tick the delete box :)</p>
    <cfabort />

  <cfelse>
  
    
    <cfloop list="#StructKeyList(qImport)#" index="index">
    
      <cfif ListFindNoCase(form.tablename, index) eq 0>
        <p>Skipping #index# table</p>
      <cfelse>
        <h2>Restoring #index# table</h2>
        
        <cfset qSchema = qImport[index].schema />
        <cfset qData = qImport[index].data />

        <cfquery name="qDelete_#index#" datasource="#dsn#">
          DELETE FROM [#index#]
        </cfquery>
        
        <cfif ListFindNoCase( ValueList(qSchema.type_name), "int identity" )>
          <cfset bIdentityInsert = True />
        <cfelse>
          <cfset bIdentityInsert = False />
        </cfif>
        
        <cfif bIdentityInsert>
          <cfquery name="qIDENTITY_INSERT_#index#_ON" datasource="#dsn#">
            SET IDENTITY_INSERT [#index#] ON
          </cfquery>
        </cfif>
        
        <cfloop from="1" to="#qData.RecordCount#" index="iCurrentrow">
      
          <cfquery name="qInsert_#index#_#iCurrentrow#" datasource="#dsn#">
            INSERT INTO [#index#] (
              #ValueList(qSchema.column_name)#
            )
            VALUES (
            <cfloop from="1" to="#qSchema.RecordCount#" index="iColumn">
              <cfset columnName = qSchema["column_name"][iColumn] />
              <cfset columnType = getCFDataType(qSchema["type_name"][iColumn]) />
              
              <cfqueryparam value="#qData[columnName][iCurrentrow]#" cfsqltype="#columnType#" />
              <cfif iColumn lt qSchema.RecordCount>,</cfif>
            </cfloop>  
            )
          </cfquery>
      
        </cfloop>
        
        <cfif bIdentityInsert>
          <cfquery name="qIDENTITY_INSERT_#index#_OFF" datasource="#dsn#">
            SET IDENTITY_INSERT [#index#] OFF
          </cfquery>
        </cfif>
        
      </cfif>
      
    </cfloop>
  </cfif>

</cfif>

</body>
</html>
</cfoutput>




<cffunction name="getCFDataType" 
  returntype="string" 
  hint="I return the cfsqltype for the cfqueryparam function">
  
  <cfargument name="type" required="true" type="string" />
  
  <cfset var private = StructNew() />
  <cfswitch expression="#ListFirst(LCase(Trim(Arguments.Type)), ' ')#">
    <cfcase value="bigint">
      <cfset private.CFDataType = "CF_SQL_BIGINT" />
    </cfcase>
    <cfcase value="binary,image,sql_variant,sysname,varbinary">
      <cfset private.CFDataType = "" />
    </cfcase>
    <cfcase value="bit">
      <cfset private.CFDataType = "CF_SQL_BIT" />
    </cfcase>
    <cfcase value="char,nchar">
      <cfset private.CFDataType = "CF_SQL_CHAR" />
    </cfcase>
    <cfcase value="smalldatetime">
      <cfset private.CFDataType = "CF_SQL_DATE" />
    </cfcase>
    <cfcase value="decimal">
      <!---<cfset private.CFDataType = "CF_SQL_DECIMAL" />--->
      <cfset private.CFDataType = "CF_SQL_FLOAT" />
    </cfcase>
    <cfcase value="float">
      <cfset private.CFDataType = "CF_SQL_FLOAT" />
    </cfcase>
    <cfcase value="int">
      <cfset private.CFDataType = "CF_SQL_INTEGER" />
    </cfcase>
    <cfcase value="money">
      <cfset private.CFDataType = "CF_SQL_MONEY" />
    </cfcase>
    <cfcase value="ntext,text">
      <cfset private.CFDataType = "CF_SQL_LONGVARCHAR" />
    </cfcase>
    <cfcase value="numeric">
      <cfset private.CFDataType = "CF_SQL_NUMERIC" />
    </cfcase>
    <cfcase value="nvarchar,varchar">
      <cfset private.CFDataType = "CF_SQL_VARCHAR" />
    </cfcase>
    <cfcase value="real">
      <cfset private.CFDataType = "CF_SQL_REAL" />
    </cfcase>
    <cfcase value="smallint">
      <cfset private.CFDataType = "CF_SQL_SMALLINT" />
    </cfcase>
    <cfcase value="smallmoney">
      <cfset private.CFDataType = "CF_SQL_MONEY4" />
    </cfcase>
    <cfcase value="datetime,timestamp">
      <cfset private.CFDataType = "CF_SQL_TIMESTAMP" />
    </cfcase>
    <cfcase value="tinyint">
      <cfset private.CFDataType = "CF_SQL_TINYINT" />
    </cfcase>
    <cfcase value="uniqueidentifier">
      <cfset private.CFDataType = "CF_SQL_IDSTAMP" />
    </cfcase>
    <cfdefaultcase>
      <cfthrow message="unknown datatype" />
    </cfdefaultcase>
  </cfswitch>

  <cfreturn Trim(private.CFDataType) />
</cffunction>

This has proved to be a real time saver for me as I no longer have to back up the database and then restore it locally. I've only tested it against MS SQL Server but the only thing that is db specific is the SET IDENTITY_INSERT ON/OFF statements.

Thanks Ray!

2 comments

  1. one quick gotcha on this: things like curly quotes and other special characters won't work properly. For example, you fetch data that has curly quotes. you save it to wddx. you then restore from that wddx, and in the DB is a ? instead of a curly quote.

    something to watch for, at any rate. I imagine there's a way around this though.

    Comment by marc esher – July 18, 2008
  2. Hi Marc, Thanks for the top tip! I'll have a look at that and trap curly braces either in the backup or restore script.

    Comment by John Whish – July 18, 2008

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.