Publish the database using SSDT

By: Armando Prato

Overview

We've created our initial objects and our scripts. Now it's time to publish our database!

Explanation

Applying our changes (or "publishing" them) is a very easy task. Before we do this, let's make sure we have an Output pane visible in our workspace. Choose VIEW->Output so this pane appears.

In the Solution Explorer, right click on the Futbol project and choose Publish.

The Publish Database dialog will appear. Here, you define your target SQL instance plus any advanced publishing behavior (via the Advanced button).

Let's enter the target SQL instance. Choosing Edit next to Target database connection brings up the Connection Properties screen. Here, I choose my target as my local instance (i.e. localhost) and click OK.

Now let's first drill into the Advanced button in more detail. Clicking on it, we can see that we have a myriad of options available to us to control publish behavior.

We can choose to have the database backed up before publish. Option Drop constraints not in source is checked by default which causes a drop of any constraints that exist in the target database but don't exist in our project. If we don't want that behavior, we would uncheck it. The options are many. Choose the defaults and click OK. You will be brought back to the Publish Database dialog. Click the Publish button and watch the Output pane.

Congratulations... the database has been published! Looking at our SQL Server 2014 instance, we can verify this:

You may be thinking to yourself, "I like that I can tailor my publishing options but I don't want to enter these every time I want to publish my database!". I agree with you, it's a nuisance. Fortunately, you can save your publish settings and refer to them every time you want to publish. We'll examine this in a bit.