How to: Deploy Changes to New or Existing Databases

  • Article

After you have made changes to the database project, you must deploy those changes to the database server by running a build script. When you generate the build script, the schema, the pre-deployment script, and the post-deployment script for the database project are compiled into a single .dbschema file. If the target database exists at deployment time, an update script is created by comparing the .dbschema file to the target database (unless you chose to always re-create the database). If the target database does not exist or if you chose to always re-create the database, the schema that is defined in the .dbschema file is used to create the database. If the database project, its properties, or its contents have changed since the last build or deployment, then you generate an up-to-date .dbschema file when you deploy the project.

Important noteImportant Note:

Before you deploy the database project, you must set the project properties to specify the target database connection and database name. If you generated the database project by using the New Database Project Wizard, then you might already have specified your project properties. You can change the project properties at any time. For more information, see How to: Configure Database Properties for Database Projects.

Required Permissions

You must have the necessary permissions to create the database (for new deployments) and create, modify, and delete the schema objects in that database to deploy the database project.

To deploy the database project to a new or existing database

  1. In Solution Explorer, click the database project that you want to deploy.

    If you created a custom configuration, you can specify that it should be used by clicking its name in the Solution Configurations list on the Standard toolbar. By default, you have a single configuration for your database project, and it is already selected.

  2. On the Build menu, click Deploy.

    The build script for the database project is generated (if it does not exist or if it is not up to date) and then deployed. The Output window displays the results of the deployment, which appear as follows:

    ProjectName -> D:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\sql\ProjectName.dbschema

    ------ Deploy started: Project: ProjectName, Configuration: Debug Any CPU ------

    Deployment script ProjectName.sql generated

    Creating ProjectName

    Creating dbo.ObjectName

    .

    .

    .

    .

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

    ========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========

To deploy the database project to a new or existing database from the command line

  1. Open a Command Prompt window, and browse to your project directory.

  2. At the command prompt, type the following example command line to deploy a database project (where DatabaseProject is the name of your project):

    MSBuild /t:Deploy DatabaseProject.dbproj

    Note

    MSBuild.exe must be on the path that your PATH environment variable specifies. In addition, you must specify the TargetDatabase and TargetConnectionString either in the database project or as part of the command line. For more information about command line syntax for generating build scripts, see An Overview of Database Build and Deployment.

    Results such as the following will appear:

    Microsoft (R) Build Engine Version 3.5.21022.8

    [Microsoft .NET Framework, Version 2.0.50727.1433]

    Copyright (C) Microsoft Corporation 2007. All rights reserved.

    Build started 5/20/2008 1:36:58 PM.

    Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\ProjectName.dbproj" on node 0 (Deploy target(s)).

    Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\ProjectName.dbproj" (1) is building "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\sql\ProjectName.deploymanifest" (2) on node 0 (DspDeploy target(s)).

    Deployment script ProjectName.sql generated

    :

    (1 row(s) affected)

    Done Building Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\sql\ProjectName.deploymanifest" (DspDeploy

    target(s)).

    Done Building Project "ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\ProjectName\ProjectName.dbproj" (Deploy target(s)).

    Build succeeded.

    0 Warning(s)

    0 Error(s)

    Time Elapsed 00:00:07.32

Deploying to Multiple Databases or Multiple Servers

You can deploy your schema to multiple databases or multiple servers. If you are deploying to a small number of servers or databases that do not change (for example, if you want to deploy to both your personal development server and a staging server), you can create multiple build configurations. Each build configuration can have a different target server or database. If you need to deploy to a larger number of servers or if the names of the target server or database change on a regular basis, you should use a different approach. These approaches are described below.

To create multiple build configurations to deploy to multiple servers or databases

  1. Open your database project in Visual Studio Team System Database Edition.

  2. On the Build menu, click Configuration Manager.

    The Configuration Manager dialog box appears.

  3. In the Active solution configuration list, click <New>.

    The New Solution Configuration dialog box appears.

  4. In Name, type the name for your first server configuration.

  5. If you had project properties already configured in the Default configuration, in the Copy settings from list, click Default.

  6. Click OK.

  7. In the Active solution configuration list box, click <New>.

    The New Solution Configuration dialog box appears.

  8. In Name, type the name for your second server configuration.

  9. If you had project properties already configured in the Default configuration, in the Copy settings from list, click Default.

  10. Click Close.

  11. Right-click your database project in Solution Explorer, and click Properties.

  12. Click the Build tab.

  13. In Configuration, click the name of your first server configuration.

  14. Modify the target connection and the database name to match your first server configuration.

  15. In Configuration, click the name of your second server configuration.

  16. Modify the target connection and the database name to match your second server configuration.

  17. On the File menu, click Save Selected Items to save your configuration settings.

    Now you can specify a configuration on the Standard toolbar and then build or deploy that configuration.

To deploy to databases from the command line

  • In a Command Prompt window, deploy the build script by typing the following command line (replacing the build script name, the server name, and the target database name with the appropriate values).

    MSBuild /t:Deploy /p:TargetDatabase=TargetDatabaseName /p:BuildScriptName=YourBuildScript.sql DatabaseProjectName.dbproj

    Note

    To deploy to multiple servers, you must override the target connection string property by specifying the following in a Command Prompt window: /p:TargetConnectionString="Data Source=ServerName;Integrated Security=True;Pooling=False".

See Also

Tasks

How to: Prepare Database Build Scripts

Concepts

An Overview of Database Build and Deployment

An Overview of Database Project Settings

Terminology Overview of Database Edition

Other Resources

Walkthroughs (Creating and Updating Version-Controlled Database Schemas)