Sql Project Publish replaces Deploy - how to suppress version control and connect to tfs build

Question

We are using SSDT Sql Server 2012, which removed the deployment option in Visual Studio for database projects (now these are sql projects). We would like to automate the publishing step, as for deployment, but it is not clear how to do this. so a few questions:

• I added the .publish.xml project to the project (after the first manual publication, checking the addition to the project). Even after this and setting it to default, when I double-click on it, it builds, but the settings window always pops up, where I need to click the "Publish" button to continue. Is there a parameter that skips this prompt and uses the current values?

• Each publication seems to generate a version of SQL output. How can I suppress this, i.e. overwrite the base file every time?

• Finally, any pointers to updating the assembly for using the new project type and publishing team for automated assemblies will be appreciated.

Answer 1

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

The Deploy option is still present, but for some reason it is not available in the menu. (Cursed Visual Studio Team!) I worked on this by adding the Expand option to one of the toolbars as follows:

• Click the arrow on the right side of the toolbar.
• Click Add or Remove Buttons, then choose Customize.
• In the Preferences dialog box, click Add Command.
• Select the Create category, then select the Expand Selection command.
• After saving your choice, the option "Expand [project name]" will appear on the toolbar. You will need to select your project in Solution Explorer for the button to turn on.

Note that deployment options are different from publishing settings. Deployment options are configured in the project properties on the Debug tab.

---

To answer your questions about the Publish option:

1) How to use a specific default publication file and avoid annoying prompts

I don’t think there is a way around this.

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

Open the .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 create your project using msbuild, as usual, so that the .dacpac file is created in the trash.

Then use sqlpackage.exe to publish using the .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

Please note that the path to sqlpackage.exe may be different.

Answer 2

The best way I've found to automate the deployment of SSDT database projects is to use msbuild. We originally used VSTSDB and used msbuild for the * .dbproj file. As it turned out, the arguments for deploying sqlproj files are exactly the same.

Since the old argument list works for us, I have not moved on to using the public.xml style. There is quite a bit of documentation for vsdbcmd.exe and msbuild vs dbproj. I would use this as a link.

Here is a list of arguments and an output of execution, as we define it to execute FinalBuilder

 [ MSBuild Project [ C:\xx\xxx\xx\xx\MyProject.sqlproj ] ] Configuration : Release OutDir : C:\Builds\1\xxxxx\builddefname\Binaries\Release\ DeployToDatabase : True TargetDatabase : ExistingDatabaseName TargetConnectionString : Data source=.;Integrated Security=SSPI;** Build started 3/23/2012 2:17:08 PM. Deployment script generated to: C:\Builds\1\xxxx\builddefname_FB\Binaries\Release\MyProject.sql Dropping FK_at_lusys_assetCategory_at_lusys_image... Creating FK_dcb28374eeabe8e715038984419... Creating FK_d82897e4acd966d4b136c242cef... Checking existing data against newly created constraints Update complete. Done Building Project "C:\xxx\xxxxxxx\xxxxxxxxx\MyProject.sqlproj" (Deploy target(s)). Build succeeded. 0 Warning(s) 0 Error(s) 

and the msbuild command line assembly looks like this:

 msbuild XXX.sqlproj /target:Deploy /p:Configuration=xxx;OutDir=xxx;DeployToDatabase=True;TargetDatabase=xxxx;TargetConnectionString="xxxxx";AlwaysCreateNewDatabase=True 

Answer 3

A little late for the party, I admit, but maybe this will help others who stumble over this discussion. My company is currently migrating to VS2012, and we have all three issues, such as Keith. I found workarounds for # 1 and # 2.

For # 1, I use AutoHotKey to control the existence of the publishing window and automatically click the "Create Script" button. Of course, you can script click the Publish button automatically. In this example, if the publishing profile is not "XYZ" (I always prefer manual intervention to deploy the production server), then go ahead and send Alt + G to create the script.

 #Persistent SetTimer, ClosePopups, 5000 return ClosePopups: if WinExist("Publish Database ") { WinActivate, Publish Database WinGetTitle, TitleText, A If not TitleText = "Publish Database XYZ.publish.xml" { Send, !G } } return 

For # 2, every time we publish it, it increments the file name with the number suffix, and we get a lot of files in our deployment folder. I just used pre-build events to clear the .sql and .txt files before building:

 if exist "$(ProjectDir)$(OutputPath)*.publish.sql" del $(ProjectDir)$(OutputPath)*.publish.sql if exist "$(ProjectDir)$(OutputPath)*.txt" del $(ProjectDir)$(OutputPath)*.txt