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?

Mcgruter

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.

Mcgruter

Any suggestions to improve the above answer are welcome. I thought I should at least put this answer up in case anyone could use it. – Mcgruter

Thanks @StingyJack! You saved me hours of frustration looking for a way to generate a FULL Deployment Script for my Project's Prod DB installation. I noticed that if you give TargetDatabaseName as BLANK the script will generate stuff to create a DB named BLANK. You might wanna mention that one should delete or change that part after FULL script is generated. Also, I noticed if I don't have valid server in Data Source=SERVER above. The script generates in \bin folder, but preview bombs. The script also is incremental. – Doormat

DeployScriptFileName doesn't work but PublishScriptFileName does. – Heavensent

Note: this was for 2010 and then 2012 projects where VS generated the DeployScriptFileName in the xml. PublishScriptFileName was not available (I still cant find docs on it within 3 minutes). – Mcgruter

If you want to skip the publish.xml file and simply pass in the values, use /t:Rebuild,Deploy rather than /t:Rebuild,Publish. Then, you can do something like msbuild /p:Configuration=Release /p:Platform=AnyCpu /p:TargetDatabaseName=... /p:TargetConnectionString="..." /p:DeployScriptFileName=generated.sql /p:UpdateDatabase=False /t:Rebuild,Deploy "...path to sqlproj". – Janetjaneta

Also, if you run into weird msbuild errors, but can build the sqlproj just fine from visual studio, try pointing to the same msbuild visual studio uses. Something like "C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\15.0\Bin\msbuild.exe" – Janetjaneta

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]

Cabot