Search code examples
msbuildsql-server-data-tools

How to stop MSBuild from publishing multiple databases

I have a visual studio 2017 solution consisting of 2 SSDT projects - one is the main one that I'm working on, one is a project containing linked objects that the main project needs to "database reference" to compile.

When I right click and publish on the main project, Visual Studio correctly published just the main project to the target server.

However, when I run an MSBuild task on the CI server (TeamCity), I can see that MSBUILD is trying to publish the referenced project (which I don't want) using the publish.xml for the main project (which doesn't make any sense!).

How do I get MSBuild to only publish the database I care about?

Command line is:

      msbuild.exe /t:Publish /p:SqlPublishProfilePath="MYDB.DROPANDCREATE.publish.xml"

The MYDB.DROPANDCREATE.publish.xml looks like this - it does "allude" to the referenced database via the SqlCmd variables. But surely that's not enough to make MSBuild decide to publish it?

So, what is the correct command line to get msbuild to just publish MYDB and stop trying to 

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>MYDB</TargetDatabaseName>
    <DeployScriptFileName>MYDB.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=MYSERVER;Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=True</TargetConnectionString>
    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>
    <CreateNewDatabase>True</CreateNewDatabase>
    <IncludeTransactionalScripts>True</IncludeTransactionalScripts>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <DeployDatabaseInSingleUserMode>True</DeployDatabaseInSingleUserMode>
</PropertyGroup>
<ItemGroup>
    <SqlCmdVariable Include="ReferencedDbVariableName">
      <Value>REFERENCEDDBNAME</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="ReferecedServerVariableName">
      <Value>REFERENCESERVERNAME</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

TeamCity logs:

[13:42:00][Step 2/2] MySln.sln.teamcity: Build targets: TeamCity_Generated_Build;Publish (1m:05s)

[13:42:00][MySln.sln.teamcity] TeamCity_Generated_Build (1m:05s)

[13:42:01][TeamCity_Generated_Build] MSBuild (1m:04s)

[13:42:02][MSBuild] MySln.sln: Build targets: Rebuild;Publish (1m:03s)

[13:42:02][MySln.sln] ValidateSolutionConfiguration

[13:42:02][MySln.sln] Rebuild (49s)

[13:42:02][Rebuild] MSBuild (49s)

[13:42:03][MSBuild] MyDb\MyDb.sqlproj: Build target: Rebuild (48s)

[13:42:52][MySln.sln] Publish (14s)

[13:42:52][Publish] MSBuild (14s)

[13:42:52][MSBuild] MyDb\MyDb.sqlproj: Build target: Publish (13s)

[13:42:52][MyDb\MyDb.sqlproj] SqlPublish (13s)

[13:42:52][SqlPublish] SqlPublishTask (13s)

[13:43:03][SqlPublishTask] Deployment script generated to: C:\BuildAgent\work\6bba30477716a590\MyDb\bin\Release\MyDb.publish.sql

[13:43:03][SqlPublishTask] Creating MYDB...

[13:43:06][SqlPublishTask] The transacted portion of the database update succeeded.

[13:43:06][SqlPublishTask] Update complete.

[13:43:06][MSBuild] ReferencedDb\ReferencedDb.sqlproj: Build target: Publish

[13:43:06][ReferencedDb\ReferencedDb.sqlproj] SqlPublish

[13:43:06][SqlPublish] SqlPublishTask

[13:43:06][SqlPublishTask] C:\BuildAgent\work\6bba30477716a590\ReferencedDb\bin\Release\ReferencedDb.publish.sql error Deploy72002: The source file 'C:\BuildAgent\work\6bba30477716a590\ReferencedDb\MYDB.DROPANDCREATE.publish.xml' could not be opened ('The system cannot find the specified file. ').

Solution

  • OK I think I get it now ...

    C:\Users\x598144\source>msbuild .\MySln.SLN /p:S qlPublishProfilePath="MYDB.DROPANDCREATE.publish.xml" /t:Publish

    ... tries to publish both databases. Whereas ....

    C:\Users\x598144\source>msbuild .\MyDb\MyDb.PROJ /p:S qlPublishProfilePath="MYDB.DROPANDCREATE.publish.xml" /t:Publish

    ... just publishes the one I care about.

    I guess because I was doing this in TeamCity I wasn't actually forming the full command line myself, so I didn't think of this before. Anyway, all good now!