locked
vsdbcmd.exe and pre/post deployment scripts

    Question

  • I am attempting to automate database deployment using vsdbcmd.exe because I want to be able to deploy the database from a previously build dbschema. Since I have all files from the build I am using the deploymanifest as follows:

     

    vsdbcmd /Action:Deploy /Quiet- /DeployToDatabase+ /DatabaseSchemaProvider:SQL /ConnectionString:"..." /ManifestFile:AppDatabase.deploymanifest /p:TargetDatabase=AppDatabase

     

    After running this I do not see the results of my pre- and post-deployment scripts, though they are referenced in the deploymanifest and present in the working directory:

     

      <ItemGroup>

        <DeploymentExtensionConfiguration Include="AppDatabase_Script.PostDeployment.sql">

          <__PostdeploymentMetadata>

          </__PostdeploymentMetadata>

        </DeploymentExtensionConfiguration>

        <DeploymentExtensionConfiguration Include="AppDatabase_Script.PreDeployment.sql">

          <__PredeploymentMetadata>

          </__PredeploymentMetadata>

        </DeploymentExtensionConfiguration>

      </ItemGroup>

     

    So does vsdbcmd.exe not include these when it deploys to database? They show up in the generated sql script, but there is no evidence that they have actually run: I am not seeing the expected results and I am not seeing error messages.

     

    Thanks,

    Andy

    Thursday, June 24, 2010 7:29 PM

All replies

  • Hello Andy,

    As I know, there is a tool named 'SQL Server Profiler' under the folder named 'Performance Tolls' of folder 'SQL Server 2008' in "ALL Programe" menu. With it, we can find out any actions being done to the SQL Server Instance, you can work with it to find whether corresponding scripts have executed or not.

    In addition, if you copy the content in the generated sql script and execute it manually in SQL Server Management Studio, do they work well? Are you working with msbuild? If so, could you please offer more detailed information about how to correlate the configuration file you mentioned above?


    Edwer Fang [MSFT]
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg @ microsoft.com

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Send us any feedback you have about the help from MSFT at fbmsdn@microsoft.com.
    Monday, June 28, 2010 8:07 AM
    Moderator
  • I'm having a similar challenge.  When I use VS 2010 Deploy action, the PostDeployment.sql script executes, but when it is deployed by the Build Definition using vsdbcmd.exe the post deployment script is not executed.
    Wednesday, December 29, 2010 7:56 PM
  • When using VSDBCMD.EXE did you copy all the information from the build directory and did you make sure that the current directory when executing is correct? Maybe the file location specified in the deploymentmanifest file is not able to resolve the location, also not that the files get renamed to include the project file name.

    <DeploymentExtensionConfiguration Include="mydb_Script.PostDeployment.sql">
          <__PostdeploymentMetadata>
          </__PostdeploymentMetadata>
        </DeploymentExtensionConfiguration>


    GertD @ www.DBProj.com
    Monday, January 03, 2011 8:18 PM
  • Yes, I have:

    <DeploymentExtensionConfiguration Include="RoadRunner_Script.PostDeployment.sql">
          <__PostdeploymentMetadata>
          </__PostdeploymentMetadata>
        </DeploymentExtensionConfiguration>

     

    in the RoadRunner.deploymanifest file and they appear in the build directory(I'm using the 'Debug' configuration so it's .../RoadRunner/sql/debug).  When I deploy from VS 2010 things work fine, but the CI environment which uses vsdbcmd does not appear to execute the postdeploy.sql.

    Thursday, January 06, 2011 2:44 PM
  • Has anyone heard anything more about this?
    Friday, November 04, 2011 10:28 PM
  • This may sound odd, but I do not think the post deployment scripts are ever executed individually.  What vsdbcmd and visual studio does is package each database into one massive change script which includes the post-deployment script appended to the end of the file.  You can check this by opening the RoadRunner.sql file that should be produced as part of the deployment process.

    Let's say your post-deployment script contains some INSERT statements, I will assume for now that you are expecting a table to contain some rows but when VSDBCMD is finished you find the table contains no data, in other words the INSERT has not executed.  This is usually because a proceeding statement failed and execution was aborted.  In all honesty, I would have thought an error would be displayed on the console, that has always been the case for me but let us again assume no error is being reported.  In that case, try passing:

    /p:AbortOnFirstError=False

    as an argument to VSDBCMD and see if your post deployment scripts get executed.  In our example, I would expect to see some rows in a table.  Of course, if this does work, it means there is an error in the script somewhere and the hard part will be tracking it down.

    To do that I would run a schema compare against the database and the visual studio database project and see what is different, if anything.

    HTH.

    Thursday, November 10, 2011 3:31 PM