Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

May 3, 2010

VS 2010 Database Project: Building and Deployment

Filed under: C#,VB.NET,Visual Studio @ 12:52 am

The new Visual Studio 2010 Database Project, as described in this prior post, makes it easy to manage the scripts for your database from within Visual Studio. This includes building the schema scripts from the individual database object scripts and deploying the scripts back to the database.

One of the ways that the new VS 2010 Database project is significantly different from the Database project available in prior versions of Visual Studio is the build and deployment process.

The old VS 2003/2005/2008 Database project was just a container for your scripts. You could right-click on the database in Server Explorer to generate the script to the project. When you made changes to the script, you could right-click on the script and select to apply the script to the database. But you had to manually perform these tasks.

And with the old style Database projects, it was easy to forget to apply your script changes to the database. It also was not easy to keep everything in synchronization. For example, if you later made a table change, it is easy to forget to adjust the related stored procedures as well.

Instead of being a simple repository, the new 2010 Database project is alive. The scripts in your Database project define your database just like the files in your code projects define your application. And the build and deployment process can apply the changes to your actual database.

This post details how to use the VS 2010 Database project and deploy your scripts back to your database.

Start by adding a VS 2010 Database project to your solution following the steps in this prior post. Then write the scripts into your project from an existing database as detailed in this prior post. Or write your own scripts in the project.

After you’ve made any changes to those scripts, you will want to deploy them back to the database.

To build the Database project, right-click on the Database project and select Build. OR you can use the Build menu.

When you build the Database project, Visual Studio validates your scripts and writes the database schema to a file with a .dbschema extension.

image_thumb7

To deploy the scripts to your database, right-click on the Database project and select Deploy. Or you can use the Build menu.

When you deploy the Database project, Visual Studio builds a deployment script.

image_thumb9

You can then run that one script to apply all of the information from your database object scripts to the development database.

But if you want to ensure that your development database is always up to date with the scripts in your Database project, you can set your Database project properties so that any changes are automatically deployed to the development database when you run your application (or build the solution).

Double-click on the Properties node of the Database project in Solution Explorer to open the Database project properties. Select the Deploy tab:

image

Set the Deploy action to "Create a deployment script (.sql) and deploy to the database". Then use the Edit button to the right of the Target connection (cut off in the above screen shot) to set the connection to your development database. Once you set these project settings, they will remain set unless you change them.

Each time you deploy the Database project, using the menu options or by just running the application within Visual Studio, Visual Studio will immediately apply all script changes directly to the database.

image

In this example, I added a table script for an Invoice table. When I ran the application, Visual Studio built and deployed the Database project scripts, creating the Invoice table in the database.

Use these features of the Database project any time you want to keep the master copy of your database schema defined in your scripts and ensure that the schema is in sync every time you run your application within Visual Studio.

Enjoy!

34 Comments

  1.   DeborahK — November 16, 2010 @ 8:04 pm    Reply

    Glen –

    I think you may misunderstand. You don’t run disconnected. You have a database. The “code” is the set of scripts. So you generate the scripts for your tables, constraints, keys, stored procedures, and so on. THAT is what comprises your database project. That way you can use TFS or another source code control on your database scripts.

    But when you run, you are running against your actual SQL Server database.

    You can set properties in the project to define that it should deploy any script changes to the database each time you run. Or you can leave that property off and handle that manually.

    Hope this clarifies things.

  2.   Gilles — March 1, 2011 @ 6:37 am    Reply

    Hi,

    I have a problem while deploying procedures on a custom database.

    In project properties i have in Deploy property Tab Target Database Name = MyCustomDatabase.
    The procedures are always created on master database.

    I checked in all project files a reference to master database.

    Can you tell me what is problem?

  3.   Tushar Agarwal — March 12, 2011 @ 4:56 am    Reply

    I have created a tool, which would allow you to run the scripts like you did in Visual Studio 2008.

  4.   Brian Barnett — May 24, 2011 @ 8:48 am    Reply

    Another way to get the ‘IF EXISTS … DROP’ back is to modify the Visual Studio item templates. They are located in C:\Program Files\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\Items. Of Program Files (x86) if you are on a 64-bit OS.

    For example the stored procedure template is called Create Procedure 2008.sql. Simply add the ‘IF EXISTS … DROP’ to the template.

  5.   Srini — June 9, 2011 @ 1:42 pm    Reply

    Good information.

    If i have to deploy to the database whenever i trigger the new build on the project.

    Is there way to do that?

  6.   Bruce — June 17, 2011 @ 4:05 am    Reply

    Hello,

    I am trying to build a database project with VS2010 premium but I get a nasty :

    C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\TeamData\Microsoft.Data.Schema.TSqlTasks.targets(56,5): Error MSB4036: The “SqlBuildTask” task was not found

    Have you encountered this problem, or do you have any suggestions as to how I could resolve this error.

    Thanks in advance

  7.   DeborahK — June 21, 2011 @ 9:41 am    Reply

    Hi Bruce –

    Some suggestions were posted here:

    http://devlicio.us/blogs/derik_whittaker/archive/2010/02/27/issues-compiling-vs2010-solutions-with-web-projects-from-nant.aspx

    Check the comments area.

    Hope this helps.

  8.   Manoj — August 2, 2011 @ 1:15 pm    Reply

    Hi, I’m using the VS 2010 Database project to deploy my database to a hosting site. It deploys great with the default scripts it generates. But I want to make a change where everytime b4 it deploys i want to drop all the tables, Sp’s, function etc. and recreate the tables using the default script. i have the drop schema objects script but when i use it as a pre-deploy..the deploy function only drops the tables & moves on to the post deploy script..it does create any tables like it was doing with the default scripts that was generated. I hoe i did not confuse you

  9.   Ananth — September 26, 2011 @ 7:13 am    Reply

    Hi,

    Is there a possibility to find the changes done on the database projects after a certain date.

    I mean can we get the deployment scripts based on date range.

    For ex: My previous release happened on lets say July 1 2011.

    So for the next release, i want the deployment script to be generated based on changes done after July 1 2011.

    This reqmt helps to get reviewed the changes from DBA, otherwise i have to give the full db deployment script to be reviewed. its painful..

    thanks in Advance… Regards

  10.   Ananth — September 26, 2011 @ 7:14 am    Reply

    Hi,

    Is there a possibility to find the changes done on the database projects after a certain date.

    I mean can we get the deployment scripts based on date range.

    For ex: My previous release happened on lets say July 1 2011.

    So for the next release, i want the deployment script to be generated based on changes done after July 1 2011.

    This reqmt helps to get reviewed the changes from DBA, otherwise i have to give the full db deployment script to be reviewed. its painful..

    thanks in Advance… Regards

RSS feed for comments on this post. TrackBack URI

Leave a comment

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