Version control database schema the SQL Compare way from Red-Gate

Tags: Version Control, General, SQL Compare, Coldfusion

In my last post on Version Control Database Schema I spoke about how you can use the build.xml file to recreate you database from the generate schemas, since then I have been chatting with their Product Manager David Atkinson. One of the major problems I had with the last method was the fact that it failed with dependencies and constraints, and usually had to modify the order of the SQL files until it would not error.

David showed me something that I wasn't aware of and made it a little more automated in the process.

The changes that needed to be made with the build.xml file where simple changes, but allowed for me to use SQL Compare to sync changes in both directions. Now I also know that not everyone uses MS SQL Server and would be very appreciative of any other JDBC solutions that will do the same thing, but for now here are the changes needed to use SQL Compare.

<target name="build database Live from Dev">
   <exec executable="${sqlCompare.directory}\SQLCompare.exe">
    <arg line="/username2:${live.username}" />
    <arg line="/password2:${live.password}" />
      <arg line="/scripts1:'${sqlScriptsCompare.directory}'" />
    <arg line="/server2:${live.server},${live.port}" />
      <arg line="/database2:'${live.database}'" />
      <arg line="/synch" />
   </exec>
</target>

As you can see it isn't much different to the SQL Task from within ant, except that this time we are running an executable from the script to do the job. And to do this the other way you just change the arguments to then reflect the following code, I usually don't do this that often but it is included in case I have too for any reason.

<target name="build database Dev from live ">
   <exec executable="${sqlCompare.directory}\SQLCompare.exe">
    <arg line="/username2:${mssql.username}" />
    <arg line="/password2:${mssql.password}" />
      <arg line="/scripts1:'${liveCompare.directory}'" />
    <arg line="/server2:${mssql.server},${mssql.port}" />
      <arg line="/database2:'${mssql.database}'" />
      <arg line="/synch" />
   </exec>
</target>

Well that's pretty much it and as I said before, I am always on the look out for standard JDBC solutions that would allow this as well.


TweetBacks


Comments (Moderated) | Add Comment

There are no comments for this entry.