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 database project schema is compared to the target database specified in the project properties. If the target database exists, an update script is created 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 build script creates a 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 build script when you next deploy the project.

Important

Before you build or 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 Projects for Build and Deployment.

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:

------ Build started: Project: DatabaseProject, Configuration: Default Any CPU ------

Building deployment script for TargetDatabaseName : EnableFullTextSearch, BlockIncrementalDeploymentIfDataLoss

DatabaseProject --> file:///ProjectDrive:/Documents%20and%20Settings/UserName/My%20Documents/Visual%20Studio%202005/Projects/SolutionName/DatabaseProject/sql/DatabaseProject.TargetServerName_TargetInstanceName.TargetDatabaseName.sql

------ Deploy started: Project: DatabaseProject, Configuration: Default Any CPU ------

Deploying script ProjectDrive:\Documents and Settings\UserName\My Documents\Visual Studio 2005\Projects\SolutionName\DatabaseProject\sql\DatabaseProject.TargetServerName_TargetInstanceName.TargetDatabaseName.sql to server TargetServerName\TargetInstanceName

file:///ProjectDrive:/Documents%20and%20Settings/UserName/My%20Documents/Visual%20Studio%202005/Projects/SolutionName/DatabaseProject/sql/DatabaseProject.TargetServerName_TargetInstanceName.TargetDatabaseName.sql --> Server:"TargetServerName\TargetInstanceName", Database:"TargetDatabaseName"

========== 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.

  2. Browse to your project directory.

  3. At the command prompt, type the following example command line to generate a build script (where DatabaseProject is the name of your project):

    MSBuild /t:Build 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 2.0.50727.42

[Microsoft .NET Framework, Version 2.0.50727.42]

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

Build started 10/11/2006 4:32:54 PM.

__________________________________________________

Project "ProjectDrive:\Documents and Settings\ UserName \My Documents\Visual Studio 2005\Projects\ SolutionName \ DatabaseProject \ DatabaseProject .dbproj" (Build target(s)):

Target SqlBuild:

Building deployment script for TargetDatabaseName : EnableFullTextSearch, BlockIncrementalDeploymentIfDataLoss

. DatabaseProject --> file:/// ProjectDrive :/Documents%20and%20Settings/ UserName /My%20Documen

ts/Visual%20Studio%202005/Projects/ SolutionName / DatabaseProject /sql/ DatabaseProject . TargetServerName _ TargetInstanceName . TargetDatabaseName .sql

Build succeeded.

0 Warning(s)

0 Error(s)

Time Elapsed 00:00:05.01

  1. At the command prompt, type the following example command line to deploy the build script that you generated in step 3:

    MSBuild /t:Deploy DatabaseProject .dbproj

    Results such as the following will appear:

    Microsoft (R) Build Engine Version 2.0.50727.42

    [Microsoft .NET Framework, Version 2.0.50727.42]

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

    Build started 10/11/2006 4:35:24 PM.

    __________________________________________________

    Project "ProjectDrive:\Documents and Settings\ UserName \My Documents\Visual Studio 2005\Projects\ SolutionName \ DatabaseProject \ DatabaseProject .dbproj" (Deploy target(s)):

    Target SqlDeploy:

    Deploying script ProjectDrive :\Documents and Settings\ UserName \My Documents\Visual Studio 2005\Projects\ SolutionName \ DatabaseProject \sql\ DatabaseProject . TargetServerName _ TargetInstanceName . TargetDatabaseName .sql to server TargetServerName \ TargetInstanceName

    file:/// ProjectDrive :/Documents%20and%20Settings/ UserName /My%20Documents/Visual%20Studio%202005/Projects/ SolutionName / DatabaseProject /sql/ DatabaseProject . TargetServerName _ TargetInstanceName . TargetDatabaseName .sql --> Server:" TargetServerName \ TargetInstanceName ", Database:" TargetDatabaseName "

    Build succeeded.

    0 Warning(s)

    0 Error(s)

    Time Elapsed 00:00:02.56

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 Edition for Database Professionals.

  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".

Security

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.

See Also

Tasks

How to: Prepare Database Build Scripts

Concepts

An Overview of Database Build and Deployment
Terminology Overview of Team Edition for Database Professionals

Other Resources

An Overview of Database Project Settings
Walkthroughs (Creating and Updating Version-controlled Databases)