Search code examples
sql-server-data-toolsdatabase-project

Streamlined deploy of SSDT Database Project to localdb

If you have a SSDT Database project in VS 2015 set as the startup project, it performs both a build and deploy when you hit F5

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

This is different than "Publish..." command, because it does not take you through the publish dialog. It just automatically deploys to the localdb instance(pointed to via the project's Debug config.

I often need to deploy the database to localdb so that I can run code generation tools against it, rather than running the entire solution(that has other applications, i.e. I would not want to run via F5).

So what I want to do is determine if there is a project level commend that I can add to my toolbar that does this automatic no-dialog deploy to localdb, other than F5's Start Debug command. I am looking through the Add Command dialog(right click toolbar->Customize), and also looked through the SSDT.* commands from the Keyboard customize in options.

Does anyone know if there is a command just for a Database project Build+Deploy or Deploy, that does it automatically? (Again the "Publish..." command is not what I'm looking for because it doesn't automatically deploy as above)

Solution

  • I have played around with different ways to do this, my two approaches I sway between are:

    1. Have a powershell script to call sqlpackage.exe to deploy the dacpac. Build the project as normal (ctrl+shift + b) then alt tab to powershell and run the last command (the deploy script)

    2. Use StudioShell (https://studioshell.codeplex.com/) to create a visual studio command to do the build then deploy which you can then map

    (2) is closer to what you want but I personally prefer (1) as it is much simpler. The benefits for me for using (1) are:

    • The script is the same one I use in CI, QA, Prod etc to deploy the dacpac so it means I test it lots (and lots)
    • I see any errors and warnings easily and can keep an eye on when objects are being re-deployed over and over so I can fix the issue rather than not noticing and the deploy times getting longer and longer

    I see similar questions like this quite a lot so I thought I would write up my thoughts properly on this: https://the.agilesql.club/blog/Ed-Elliott/Visual-Studio-SSDT-Publish-My-Personal-Best-Practices

    Whatever you do (this or something else) follow up with your experiences, I (at least!) would find it interesting!

    ed