This was not supposed to be a problem. In Visual Studio, you can easily generate a database deployment script by setting project properties on the Deploy tab.
However, when using TFS/MSBuild automated build, this did not work – no .sql file was generated. I found a clue in the documentation, An Overview of Database Build and Deployment. There is an example to deploy a database:
MSBuild /target:Deploy /p:UseSandboxSettings=false /p:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=(local)\SQLEXPRESS;Integrated Security=True;Pooling=False" MyProjectName.dbproj
The important parameter here is the first one, /target:Deploy. Specifying it on the command line is fine if you’re just building this project, but what if it is part of a larger solution? Turns out to be relatively easy. Just edit the project file and add Deploy as a default target:
<Project DefaultTargets="Build;Deploy" xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="4.0">