Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 31, 2013

Publishing a Database Project

Visual Studio 2012 introduced a new SQL Server Database Project. This post first covers what happens when you publish a Database Project. It then details how to publish your Database Project.

See this link for an introduction to the Visual Studio 2012 SQL Server Database Project.

What Does Publish Do?

  • When publishing a Database Project, you define a connection and a database name specifying the "target" of the publish operation.
  • The SQL Server Data Tools (SSDT) looks at the schema of the selected (target) database.
  • If the selected database does not exist, SSDT creates a database using the table, stored procedure, data, and other scripts in the Database Project.
  • If the database does exist, SSDT compares the selected database schema against the information in the DACPAC and builds an update script. It then runs the update script against the selected database.
  • In either case, the selected (target) database schema then matches the schema as defined by the Database Project.

WOW! When you think about this, it is amazing! Especially if you have been doing this manually. No more schema compares in an attempt to figure out the database changes. No more manually developed update scripts. No more issues from running an update script on a database that is a version behind.

With the new Database Project publish feature, each target database is separately compared and a unique update script is generated and executed to ensure *that* target database is updated to the desired schema.

There are a large number of publish settings that you can set to tailor this process. For example:

  • Block incremental deployment if data loss might occur.
    • If this setting is selected, a change defined in the generated update script may not execute successfully if the change could cause data loss.
    • For example, if changing a column from nvarchar(20) to nvarchar(15), data loss could occur.
    • If this setting is set, the column change will not be applied.
  • DROP objects in target by not in project.
    • If this setting is selected, any tables, stored procedures, or other database objects that are in the selected database but not in the project will be deleted.
    • For example, if you change the name of a stored procedure from CustRetrieve to CustomerRetrieve, the selected database will still have the CustRetrieve stored procedure in addition to the new one.
    • If this setting is set, then the CustRetrieve stored procedure will be deleted.

Now that you know what it does, let’s look at how to do it.

How Do I Publish?

To publish a Database Project:

1) Right click on the Database Project in Solution Explorer and select Publish.

2) A dialog appears allowing you to enter a publish profile, which are settings to use during the publish operation.

image

3) Click the Edit button to specify the SQL Server instance you wish to publish to (target).

image

In this case, we are publishing to our development SQL Express database called ACM.

4) The publish script name is defaulted, but you can change it. The publish script file is the file that SQL Server Data Tools (SSDT) create during the publish operation.

image

5) Click Create Profile to save the entered settings as a named profile. That way you won’t have to enter the settings again next time.

Saving a profile adds a publish.xml file into your Database project with the entered settings.

image

6) Click Publish to start the publishing process.

The publish processing displays in the Data Tools Operations window with links to view the script or results.

image

You can create any number of publish profiles. For example, you can create one to publish to your local development database, one to publish to a local test database, and one to publish to the master test database. Just be sure to give each one a clear name.

image

Double-click on a publish profile to open the Publish dialog using the selected profile.

If there is a publish profile that you use often, such as the profile you use to update your development database, there is a way to set a default.

To set a default publish profile:

1) Right-click on the publish profile file in Solution Explorer and select Set as Default Publish Profile.

image

image

2) Then when you select to publish your Database Project, it will default to the setting defined in your default profile:

image

Use the publish feature any time you want to update (or create) a target database using the database schema defined in your Database Project.

Enjoy!

2 Comments

  1.   Juliana — March 23, 2015 @ 11:21 am    Reply

    Hello, I’ve come across your blog posts about SSDT and they are one of the most helpful ones out there.. It is so difficult to find information about it, which makes it hard to use the tool..

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs