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 — May 27, 2010 @ 10:31 am    Reply

    If you use the new built-in deployment features, the generated scripts handle this.

    So this is only a pain point when executing an individual script.

  2.   Noam Ben-Ami — May 27, 2010 @ 4:27 pm    Reply

    Joe – You can update only some parts of the database by using the “Schema Compare” feature in the Data menu. Just select the objects you want to deploy (or rather, unselect the ones you don’t want deployed.)

    HTH.

  3.   Robert Friberg — June 10, 2010 @ 9:53 am    Reply

    @James

    There is a “Generate DROP statements for objects that are in the target database but that are not in the database project” option. You can find it under properties -> Database.sqldeployment in VS2010 solution explorer.

  4.   Buddy Lindsey — June 29, 2010 @ 2:11 pm    Reply

    This is a great resource and good information. However, you only keep talking about development database.

    Is it possible to do all the work on the dev database then when you are done can you point it at the production DB and it will update database structure of the Production DB to what the Dev DB structure is?

    Thanks.

  5.   Rashmi — August 17, 2010 @ 7:06 pm    Reply

    Hello !

    Very useful infromation ! I have a quick question… lets say I setup a datatabase project and after that someone added some stored procedure to the database with SQL server management studio. Even after I deploy the project, it does not pickup these newly added stored procs. Is there any way to do this synchronization?
    From you post, I found out how to deploy stored procs to database…I am trying to find reverse for it.
    Thank you very much,
    -Rashmi

  6.   DeborahK — August 17, 2010 @ 10:44 pm    Reply

    Hi Rashmi –

    There is no *easy* way to do this. I have found that I need to keep a copy of the database under a different name, call it Database2. Then when someone updates Database1 I can run the Data | Schema Compare to compare Database1 to Database2. It sees that I have some new stored procedures and will create the new scripts into my project.

    Hope this helps.

  7.   dbdw — August 31, 2010 @ 11:50 am    Reply

    Rashmi,

    You could compare the database schema against the project schema to create scripts for the SPROCs added through management studio.

  8.   dbdw — August 31, 2010 @ 11:56 am    Reply

    Hi Deborah,

    I like everything about the new database projects, except when depolying version 2 and upwards!

    When a table is altered the deployment scripts creates a temporary tabe and then copies the data. And then drops the original table and rename the temp table. This is not going to work when deployed against the Production database ! Example: If I have 50 Million rows the deployment is going to take hours together. Is there a work around for deployment to create ALTER TABLE scripts instead?

    Thanks in advance…

  9.   Rita — November 3, 2010 @ 2:10 pm    Reply

    Hello,

    I’m having some problems compiling a database project because some SP have temporary tables. For instance a SP with:

    SELECT TOP 5000 IDENTITY(INT,1,1) AS N
    INTO #Tally
    FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2

    INSERT INTO @Parameters (ParameterValue)
    SELECT * FROM #Tally

    returns:
    Error 198 DAC05000: Invalid object name ‘#Tally’.
    for “INSERT INTO @Parameters (ParameterValue)
    SELECT * FROM #Tally ” (running the SP directly in the database it works fine)

    Is it possible to use temporary tables in SP (in database projects) ? How ?

    Thanks in advance.

  10.   Glen — November 11, 2010 @ 4:17 pm    Reply

    It seems to me that the problem with the VS2010 database projects is that they “treat your database as code”. The problem is that databases are not code. They are data stores with some code attached.

    The idea of developing “disconnected” doesn’t make any sense to me at all. I’m not writing this stuff with the hope that it will magically work once it’s all put together. I write and test and write and test with very small steps in between.

    While I appreciate the basic syntax checking that VS2010 provides I can get feedback just as quickly in management studio by making a change and pressing F5.

    Bottom line: Me no like.

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