How do I create the deployment scripts from VS 2012 database projects?

Question

With VS2010 database projects, I could define a target (or not) database, set an action (create script only or create script and deploy to target), execute a "deploy" (through VS or MSBuild) and get a result script.

I never used the "create and deploy" action, because I am only using the resultant script to build an installer and will apply the script later as part of the installation process.

This feature allowed me to create both an upgrade (only the changes since the last version) or a full installation script (if pointed at no target db).

I cannot seem to find the correct combination of options to reproduce this "script only" behavior for upgrades and full installations when using VS 2012 or SSDT in general.

I have found this question which covers how to click the buttons in VS, but it does not address how to get this done in MSBuild.

Can someone point me to a useful resource for this specific configuration?

Answer 1

After hacking at it for several hours, I was able to get something workable. It comes down to two significant elements: getting a correct publish xml, and the correct arguments for MSBuild.

The publish xml file was the standard format that was created with VS. It looks like something this...

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>BLANK</TargetDatabaseName>
    <DeployScriptFileName>DeployTarget.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=SERVER;Integrated Security=True;Pooling=False</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <ScriptDatabaseOptions>False</ScriptDatabaseOptions>
    <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss>
    <CommentOutSetVarDeclarations>False</CommentOutSetVarDeclarations>
  </PropertyGroup>
</Project>

With the previous project format, you could leave Target DB and connection string blank and it would generate a "complete" deployment script. However in SSDT sqlproj files, you must provide something, even if its incorrect, hence the "BLANK" database name. If I change this to an actual database, it would produce a delta script.

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\msbuild.exe /p:Configuration=Release;Platform=AnyCPU;SqlPublishProfilePath="<fullPathToPublishXML";UpdateDatabase=False /t:Rebuild,Publish "<fullPathToSqlProj>"

This still does not honor the DeployScriptFileName that is in the publish xml (It creates a sql file with the same name as the publish profile), but does appear to create the correct content in the file.

Answer 2

You are on the right track, but you are missing a parameter. If you want to provide the script name you should use the following parameter in your msbuild execution:

/p:PublishScriptFileName=[your output script.sql]