Backup and Restore data with WDDX

18 Jul 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!

Posted in:
ColdFusion

8 comments

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
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
Shouldn't this ValueList(qSchema.column_name) on line 111 be ValueList(qSchema.columnList)?

Comment by Bryan – January 07, 2009
Disregard my last comment, I was doing it in a slightly different manner.

Comment by Bryan – January 07, 2009
Hi, I used ray's Post to Create a Backup, Now using your post i tried to restore it and i got this issue!

The zip entry for path data.packet.xml was not found in the zip file specified.

Comment by Misty – April 03, 2011
Okay! I successfully executed the code, but i am getting the following error!

<cfset qSchema = qImport[index].schema />

my db was in mysql and i tried restoring it in mSSQL, might be that could be the issue, what if i want to restore in mysql instead or if in mMSSQL how would i remove the error!

Comment by Misty – April 03, 2011
I ran the backup just fine but when I go to do the restore, I get the message:

"Unknown Datatype"

Any suggestions?

Comment by Ray Meade – November 17, 2011
Hi Ray,

Do you get any more information? Might be worth turning on CF debugging and see if there is any more info.

Comment by John Whish – December 01, 2011

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.

Backup and Restore data with WDDX

8 comments

Leave a comment

Search

Wish List

Categories

Recent Posts