locked
Generating update SQL script using MSBuild targets file RRS feed

  • Question

  • We recently upgraded for VS2010 to VS2013 and had to migrate over our dbproj to a sqlproj. Previously on Build of the main project of the solution the dbproj would also be deployed, however only the update script would be created (the changes would not be published). We did this through a targets file, that referenced the dbproj and did a deploy, with the properties on the dbproj set to only generate a script when deployed.

    However on migrating to the new sqlproj this property no longer appears to be there. If we do a deploy using the targets file, only produces a dacpac file (we need a sql script) and if we do a publish, it creates the update script but then publishes it to the database. Is there a property that needs to be used on the publish to not update the database?

    Here is the target file that I'm using:

    <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

      <PropertyGroup>

        <AfterAddContentPathToSourceManifest Condition="'$(AfterAddContentPathToSourceManifest)'==''">

          $(AfterAddContentPathToSourceManifest);

          DeployDbProjBeforePackage;

        </AfterAddContentPathToSourceManifest>

      </PropertyGroup>

      <!-- Specifies each dependent database project here, its properties will be appended to the target msbuild Properties -->

      <ItemGroup>

        <DBProject Include="..\Database\Database.sqlproj">

            <Properties>PublishScriptFileName=..\Database\sql\$(Configuration)\Database.sql</Properties>

            <Properties>SqlPublishProfilePath=..\Database\$(Configuration).publish.xml</Properties>

        </MyDependentDBProjects>

      </ItemGroup>

      <Target Name="DeployDbProjBeforePackage">

        <Message Text="Creating directory: %(DBProject.rootdir)%(DBProject.directory)sql\$(Configuration)" />

        <MakeDir Directories="%(DBProject.rootdir)%(DBProject.directory)sql\$(Configuration)" />

        <MSBuild Projects="@(DBProject)" Targets="Build; Publish" />

      </Target>

    </Project>


    Tuesday, April 29, 2014 10:11 AM

Answers

  • Hi Caleb,

    If you just want to generate the update script without publishing to the target database, in the IDE you can choose the Generate Script button.  

    If you want to achieve that via msbuild command, you should use /p:UpdateDatabase=False .

    Thanks.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Anna Cc Monday, May 5, 2014 4:01 PM
    Wednesday, April 30, 2014 9:30 AM

All replies

  • Hi,

    I'm sorry that I'm not familiar with dbproj and sqlproj files. Are they similar as the csproj file?

    Please check if this thread "Is there a command-line tool for upgrading dbproj projects to sqlproj?" helps you.

    And this one MSBUILD Executing only Changed SQL Scripts .

    I will try to involve some senior engineer into this case. Thanks for the patience!

    Best regards,

    Sunny

    Wednesday, April 30, 2014 7:39 AM
  • Hi Caleb,

    If you just want to generate the update script without publishing to the target database, in the IDE you can choose the Generate Script button.  

    If you want to achieve that via msbuild command, you should use /p:UpdateDatabase=False .

    Thanks.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Anna Cc Monday, May 5, 2014 4:01 PM
    Wednesday, April 30, 2014 9:30 AM