Search code examples
msbuildazure-sql-databasesql-server-data-tools

MSBuild ignores p-Switch when publishing database

I've created a publish profile via SSDT in VS 2015 targetting a SQL Azure V12 server and now I want to publish the database using the console. I'm using the following command:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\msbuild.exe  
    /t:Publish 
    /p:TargetDatabaseName=NEWDBNAME        
    /p:SqlPublishProfilePath="PublishProfiles\test.publish.xml" 
    "{PATH_TO_MY_SSDT\{PROJECTNAME}.sqlproj"

My profile looks like this (curly braces are just placeholders for real values here):

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="14.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>DBNAME</TargetDatabaseName>
    <DeployScriptFileName>Data.Database.sql</DeployScriptFileName>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <CreateNewDatabase>True</CreateNewDatabase>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <TargetConnectionString>Data Source={SERVER};Persist Security Info=True;User ID={USER};Password={PW};Pooling=False</TargetConnectionString>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="BaseData">
      <Value>1</Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="SampleData">
      <Value>1</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

This creates a database "DBNAME" everytime instead of replacing the property TargetDatabaseName with the value "NEWDBNAME". Everything else is working fine.

Why will msbuild leave the property unchanged?

Edit:

I tested a little bit around and tried the following (after Christian K.s comment):

Test 1: Adding diag

C:\Windows\Microsoft.NET\Framework\v4.0.30319\msbuild.exe  
    /t:Publish 
    /p:TargetDatabaseName=NEWDBNAME        
    /p:SqlPublishProfilePath="PublishProfiles\test.publish.xml" 
    "{PATH_TO_MY_SSDT\{PROJECTNAME}.sqlproj" 
    /v:diag 
    /fl

There is a section in the output showing the parameters. Here is the screenshot:

Normal parameter output

Test 2: Changing UpdateDatabase for testing purpose

C:\Windows\Microsoft.NET\Framework\v4.0.30319\msbuild.exe  
    /t:Publish 
    /p:TargetDatabaseName=NEWDBNAME        
    /p:SqlPublishProfilePath="PublishProfiles\test.publish.xml" 
    /p:UpdateDatabase=false 
    "{PATH_TO_MY_SSDT\{PROJECTNAME}.sqlproj" 
    /v:diag 
    /fl

UpdateDatabase was changed the correct way

As you can see, UpdateDatabase was changed correctly and TargetDatabaseName does not appear in the list of properties.

Solution

  • I got the solution. I just set CreateNewDatabase in my publish-profile to false and it worked.

    Edit: It only works one time because after that the process is simply skipped because the database already exists. The solution now is to edit the publish.xml before I execute it via code as Kris suggested in his comment.

    This is another sample for the incompatibility between SQL Server and SQL Azure which is annoying in my eyes.