How to call MSBuild in order to build and deploy a database projects from a Build Process Template, xaml file

_{Thursday, April 12, 2012 7:30 AM | 1 vote}

I desperately need a how to guide on:

“How to call MSBuild in order to build and deploy a database projects from a Build Process Template, xaml file”
I have read Headless MSBuild Support for SSDT (http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/), but I’m not experienced enough to understand how to do the same thing from Build Process Template

_{Thursday, April 12, 2012 3:10 PM ✅Answered}

To publish several at the same time you could create your own .targets file with a custom Target.  You'd then need to include the .targets file via an <Import/> element in your .sqlproj files.  Your targets file could also alter the sqlpublishprofilepath for each database being published.

If you're not familiar with MSBuild I'd suggest executing the .sqlproj target with /pp and /v:diag arguments.  

msbuild /v:diag /t:build mydb.sqlproj > diagoutput.txt

msbuild /pp /t:build mydb.sqlproj > ppoutout.txt

These output files will give you great detail about how our project file defines targets and the msbuild properties you can use to control the system.

_{Thursday, April 12, 2012 1:06 PM}

Just use "Default Template" for build process.

Then in Process > Advanced build process parameters > msbuild arguments add this if you want to build and deploy:

/t:Build;Publish /p:SqlPublishProfilePath=Build.publish.xml

This Build.publish.xml file is in source control and in my case is using sql credentials:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>DatabaseName</TargetDatabaseName>
    <DeployScriptFileName>Script.database.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=SQLServer;Persist Security Info=True;User ID=userName;pwd=passWord;Pooling=False</TargetConnectionString>
    <ScriptDatabaseOptions>True</ScriptDatabaseOptions>
    <BlockIncrementalDeploymentIfDataLoss>True</BlockIncrementalDeploymentIfDataLoss>
    <PublishDependentProjects>False</PublishDependentProjects>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <SingleUserMode>False</SingleUserMode>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
  </PropertyGroup>
</Project>

This way you should be able to build and deploy database from build server, by using given publishing profile.

_{Thursday, April 12, 2012 1:53 PM}

Thanks for the feedback but:

A) if I have several database projects in the solution, how can I specify one xml file/database as argument to msbuild arguments?

Something like:

/t:Build;Publish /p:SqlPublishProfilePath=Build.publishdatabase1.xml, Build.publishdatabase2.xml

B) If I want to build several configurations at the same time, ex to dev and test, how do I get msbuild arguments to pick the right file for the corresponding configuration( One file for dev and one file for test).

/Peter

_{Thursday, April 12, 2012 2:18 PM}

You can probably build several databases - because build does not need a publish configuration file, but not sure if you will be able to publish several at once.

I would say you can have several build definitions for each database project - that way you will specify the right publish config in each build definition.

_{Monday, April 29, 2013 9:47 AM}

Publish doesn't work on TFS build for me.

In VS2012, I have a solution with 4 SQLCLR projects and 1 Database project which references the 4 SQLCLR projects. So I only need to publish the Database project to create the database.

But on TFS2010, the build fails because it try to publish the 5 projects !

In the TFS2010 build definition, I defined the "MSBuild Arguments" as this : /t:Build /t:Publish /p:SqlPublishProfilePath="Tools\Deployment Settings\MyDatabase.Dev.publish.xml"

The "MyDatabase.Dev.publish.xml" file exist only in the Database project.

Thank you for your help.