Geeks With Blogs
David Atkinson

If your organization has continuous integration in place, it will most likely contain a build step in which your latest application source code gets pulled from source control, compiled, and optionally subjected to a various tests.

If you’re a Microsoft shop, chances are that your application works alongside an instance of SQL Server.

SQL Source Control allows you to put your schema objects in source control by maintaining a set of per-object creation scripts representing in your existing version control system, such as Team Foundation Server, Subversion, SourceGear Vault, etc.

Here I will describe how to use the appropriate Red Gate command line interface to recreate the database from these source creation scripts, which is essential in order to achieve database continuous integration.

In SQL Compare Pro’s Program Files folder, you will find the command line interface executable, sqlcompare.exe. Below I will provide examples of how this is called from a DOS batch file and NAnt.

You can either deploy a new database or deploy to an existing one. If doing the former, use sqlcmd.exe to drop and recreate the database as follows.

DOS Command

Sqlcmd.exe -E -SYourCIServer -Q "ALTER DATABASE YourCIDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE YourCIDatabase "

Sqlcmd.exe -E -SYourCIServer -Q " CREATE DATABASE YourCIDatabase"

NANT

 
<target name="drop_CI_DB">
<exec program= "sqlcmd.exe">
<arg value="-E"/>
<arg value="-SYourCIServer "/>
<arg value="-Q &quot;ALTER DATABASE YourCIDatabase SET 
SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE 
YourCIDatabase&quot;"/>
</exec>
</target>
<target name="create_CI_DB" depends="drop_CI_DB">
<exec program= "sqlcmd.exe" failonerror="true">
<arg value="-b"/>
<arg value="-E"/>
<arg value="-SYourCIServer"/>
<arg value="-Q &quot;CREATE DATABASE 
YourCIDatabase&quot;"/>
</exec>
</target>

The -b switch ensures that an exit code of 1 will be return if the command fails, aborting the script.

We use sqlcompare.exe to apply the database changes by setting the source as the creation files scripts folder, and the target as the database (YourCIDatabase).

DOS Command

Sqlcompare.exe /scripts1:YourScriptsFolder /server2:YourCIServer /db2:YourCIDatabase /sync

NAnt

 
<target name="DeployToCIDatabase" depends="create_CI_DB">
<exec program="sqlcompare.exe" failonerror="true">
<arg value="/scripts1:YourScriptsFolder"/>
<arg value="/server2:YourCIServer"/>
<arg value="/db2:YourCIDatabase"/>
<arg value="/sync"/>
</exec>
</target>

The /sync switch ensures that the changes are applied. It is possible to use /ScriptFile:YourDeploymentScript.sql to generate the script.

You may have noticed the absence of a command to pull the latest scripts folder from source control. This is a task that is generally taken care of by the continuous integration tool, rather than the build script itself.

In an upcoming blog post I will describe how to configure JetBrains TeamCity with a NAnt script to monitor source control for schema changes, and trigger a process to keep our continuous integration database up to date.

 <div style="margin: 0px; padding: 0px; float: none; display: inline;" id="scid:0767317B-992E-4b12-91E0-4F059A8CECA8:66811b12-0a44-4aa7-b7b6-749356782948" class="wlWriterEditableSmartContent">Technorati Tags: <a href="http://technorati.com/tags/SQL+Server" rel="tag">SQL Server</a></div>v

Posted on Monday, April 16, 2012 6:18 PM SQL Server , Continuous integration , SQL Compare , SQL Source Control , NAnt , TeamCity | Back to top


Comments on this post: How to build a database from source control

# re: How to build a database from source control
Requesting Gravatar...
So there is no way to do this with purely SQL Source Control? We have and use version 2 of that. I find it a major fail that there is no commandline based build for that.
Left by Nick Portelli on Apr 18, 2012 4:40 PM

# re: How to build a database from source control
Requesting Gravatar...
Thanks for the question. SQL Source Control is the SSMS integration component of the full SQL Developer Bundle. The component responsible for deployment is SQL Compare Pro, whose command line covers the Red Gate automated deployment story.
Left by David Atkinson on Apr 18, 2012 7:14 PM

# re: How to build a database from source control
Requesting Gravatar...
Thanks for this post, it's working great. Next step to do is to get the default data to deploy (e.g. lookup tables). I assume it would be the same for SQL Data Compare. This is awesome :)
Left by James on May 11, 2012 1:13 PM

# re: How to build a database from source control
Requesting Gravatar...
Yes, it's exactly the same for SQL Data Compare except you would use the sqldatacompare.exe command line instead. SQL Source Control maintains a 'data' folder that contains one file of DML statements for each source controlled table.
Left by David Atkinson on May 17, 2012 12:36 AM

Your comment:
 (will show your gravatar)


Copyright © David Atkinson | Powered by: GeeksWithBlogs.net