Sql Project Publish replaces Deploy - how to supress versioning and hook into tfs build

Question

We're using the Sql Server 2012 SSDT which removed the deploy option in Visual Studio for the database projects (now sql projects). We'd like to automate the Publish step as we had for deploy, but it's not clear how to do this. so thA couple of questions:

1. I've added the .publish.xml to the project (after the first manual publish, checking add to project). Even after that, and setting it to the default, when I double click it, it builds, but always pops up settings window, where I need to click the "Publish" button to continue. Is there a setting that would skip this prompt and use the current values?

2. It seems that each publish generates a version of the sql output. How can I suppress this- i.e. overwrite the base file each time?

3. And lastly, any pointers for updating the build to use the new project type and publish command for the automated builds would be appreciated.

Answer 1

How to restore the Deploy option: (Visual Studio 2010/2012 only -- this is no longer supported in Visual Studio 2013)

The Deploy option is still present but for some reason it's not available in the menus. (Cursed Visual Studio team!) I've worked around this by adding the Deploy option to one of the toolbars as follows:

1. Click the arrow on the right-hand side of a toolbar.
2. Click "Add or Remove Buttons", then Customize.
3. In the Customize dialog click Add Command.
4. Select the "Build" category, then select the "Deploy Selection" command.
5. After saving your selection the "Deploy [project name]" option will appear on the toolbar. You'll need to select your project in Solution Explorer for the button to become enabled.

Note that the deployment settings are different than the publish settings. The deployment settings are configured in the project's properties on the Debug tab.

---

To answer your questions about the Publish option:

1) How to use a specific publish file by default and avoid the annoying prompt

I don't think there's a way around this.

2) How to publish the entire database, not just the changes

Open your .publish.xml file in a text editor and add <AlwaysCreateNewDatabase>true</AlwaysCreateNewDatabase>.

For example:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>MyDatabase</TargetDatabaseName>
    <DeployScriptFileName>MyDatabaseProject.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=localhost\SQL2012;Integrated Security=True;Pooling=False</TargetConnectionString>
    <PublishDependentProjects>False</PublishDependentProjects>
    <ProfileVersionNumber>1</ProfileVersionNumber>
    <AlwaysCreateNewDatabase>true</AlwaysCreateNewDatabase>
  </PropertyGroup>
</Project>

3) Command-line syntax for automated builds

First build your project with msbuild as you normally would so that the .dacpac file is created in the bin.

Then use sqlpackage.exe to publish using your .publish.xml file:

C:\Program Files\Microsoft Visual Studio 10.0\Microsoft SQL Server Data Tools\sqlpackage.exe /Action:Publish /SourceFile:C:\[path to my project]\bin\Debug\MyDatabaseProject.dacpac /Profile:C:\[path to my project]\MyDatabaseProject.publish.xml

Note that the path to sqlpackage.exe may be different.