Rebuild Your Database With nAnt

Re-building your database using nAnt!

Have you ever peeked into the SQL Server tools folder. I found a couple of interesting tools in there.

osql.exe
sqlcmd.exe

Both of these useful executables allow you to connect to a SQL Server. So using either of these executables and the power of nAnt, I can re-build a database from scratch.

<?xml version="1.0"?>
<project name="University" default="all">
  <!-- environment-specific properties -->
  <property name="sqlcmd.exe" value="C:\program files\microsoft sql server\90\Tools\Binn\sqlcmd.exe" />
  <property name="sqlcmd.ConnectionString" value="-E" /><!-- Windows Authentication -->
  <property name="initial.catalog" value="University"/>
  <property name="initial.server" value="(local)" />
  <property name="database.path" value="C:\nant\databases" />

  <target name="convert.template">
    <echo message="Convert the SQL file template to one with the actual database name." />
    <copy file="${target}.template" tofile="${target}" overwrite="true">
      <filterchain>
        <replacetokens>
          <token key="INITIAL_CATALOG" value="${initial.catalog}" />
          <token key="DBPATH" value="${database.path}"/>
        </replacetokens>
      </filterchain>
    </copy>
  </target>

  <target name="exec.sql.template">
    <call target="convert.template" />
    <echo message="Connecting to database server..." />
    <echo message="${sqlcmd.exe} ${sqlcmd.ConnectionString} -b -i ${target}" />
    <exec program="${sqlcmd.exe}" commandline="${sqlcmd.ConnectionString} -S ${initial.server} -b -i ${target}" />
  </target>

  <target name="builddb">
    <copy todir="${database.path}">
      <fileset basedir="sql\original">
        <include name="*"/>
      </fileset>
    </copy>
    <property name="target" value="sql\attach.sql"/>
    <call target="exec.sql.template"/>
  </target>
</project>

In order to create the database you will have to update the properties in the build file to settings specific to your machine, then open a command window to the directory containing the build file and type “build builddb” then press enter.

Resources:

sqlcmd.exe