An Overview of Database Build and Deployment

Article
02/04/2013

To create a database or to publish updates to an existing database from your database project to a database server, you must build the database project and then deploy it to that server.

The build step compiles your database project into the files that are used for deployment. These files include the .dbschema file, the .deploymentmanifest file, and the pre-deployment and post-deployment scripts. No comparison with a target database is performed when you build the database project. When you deploy the database project, the output of the build action is compared to the target database, if the target database exists, and the actions that are required to update the target to match the database project are identified. Depending on your settings, those updates are deployed to the target database.

As an alternative, you can generate the deployment script (a .SQL file) and then modify it before you deploy it to a staging or production server by using the Transact-SQL Editor or another tool such as SQL Server Management Studio. The Clean build action deletes any existing build artifacts, such as scripts, deployment manifests, and the .dbschema files.

Deployment Scripts

You can create scripts that run before or after the scripts that create or update the target. You can have only one pre-deployment script and one post-deployment script, but you can include other scripts from within these scripts. For more information, see Creating and Modify Database Scripts.

Deployment Manifest

When you build your database project, a deployment manifest is generated. This manifest contains all of the project-level configuration information that is required to enable you to deploy without also needing the database project file (.dbproj).

A deployment manifest file (.deploymanifest) is an XML file whose structure is very similar to an MSBuild project file. The .deploymanifest file is created in the sql\Configuration folder in your database project folder, where Configuration is the build configuration, such as debug or release.

The following example shows the .deploymanifest file for an empty SQL Server 2008 database project named Empty2008DbProj:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="https://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetConnectionString>Data Source=stevenpoauth\sql2k8;Integrated Security=True;Pooling=False</TargetConnectionString>
    <TargetDatabase>Empty2008DbProj</TargetDatabase>
    <DeployToDatabase>True</DeployToDatabase>
    <DeployToScript>True</DeployToScript>
    <SourceModel>Empty2008DbProj.dbschema</SourceModel>
    <DeployScriptFileName>Empty2008DbProj.sql</DeployScriptFileName>
    <DeploymentConfigurationFile>Empty2008DbProj_Database.sqldeployment</DeploymentConfigurationFile>
  </PropertyGroup>
  <PropertyGroup>
    <SqlCommandVariablesFile>Empty2008DbProj_Database.sqlcmdvars</SqlCommandVariablesFile>
  </PropertyGroup>
  <ItemGroup>
    <DeploymentExtensionConfiguration Include="Empty2008DbProj_Script.PostDeployment.sql">
      <__PostdeploymentMetadata>
      </__PostdeploymentMetadata>
    </DeploymentExtensionConfiguration>
    <DeploymentExtensionConfiguration Include="Empty2008DbProj_Script.PreDeployment.sql">
      <__PredeploymentMetadata>
      </__PredeploymentMetadata>
    </DeploymentExtensionConfiguration>
  </ItemGroup>
  <ItemGroup>
    <DeploymentExtension Include="Microsoft.Data.Schema.Sql.Build.SqlPlanOrderModifier">
      <Assembly>Microsoft.Data.Schema.Sql</Assembly>
      <Version>10.0.0.0</Version>
      <Token>sD9ffxHVCjo=</Token>
    </DeploymentExtension>
    <DeploymentExtension Include="Microsoft.Data.Schema.Sql.Build.SqlPrePostDeploymentModifier">
      <Assembly>Microsoft.Data.Schema.Sql</Assembly>
      <Version>10.0.0.0</Version>
      <Token>sD9ffxHVCjo=</Token>
    </DeploymentExtension>
    <DeploymentExtension Include="Microsoft.Data.Schema.Sql.Refactoring.SqlRefactoringDeploymentContributor">
      <Assembly>Microsoft.Data.Schema.Sql</Assembly>
      <Version>10.0.0.0</Version>
      <Token>sD9ffxHVCjo=</Token>
    </DeploymentExtension>
  </ItemGroup>
</Project>

The deployment manifest file contains one or more PropertyGroup nodes that define the default configuration that is used when you deploy. The deployment manifest file also includes ItemGroup nodes that contain DeploymentExtensionConfiguration nodes and Reference nodes.

DeploymentExtensionConfiguration nodes define configuration files that are passed to deployment extensions when you deploy. These configuration files include the pre-deployment and post-deployment scripts, as well as the refactoring log that is used to preserve intent when you deploy.

Reference nodes define any artifacts that are referenced by the project and that are copied into the output folder when you build the project. The referenced files are processed when you deploy to ensure that the model of the database is correctly re-created.

Transactions in the Deployment Script

Most database changes in the deployment script occur inside a transaction so that the changes can be rolled back if deployment fails. However, the following objects are created, updated, or deleted outside the deployment transaction.

SQL Server Version	Objects Outside the Deployment Transaction
SQL Server 2008	Server Role Membership Endpoints Full-text Indexes Full-text Catalogs Linked Servers Linked Server Logins Event Sessions Server Audit Specifications Full-text Stop Lists
SQL Server 2005	Server Role Membership Endpoints Full-text Indexes Full-text Catalogs Linked Servers Linked Server Logins

SQL Server 2008

Server Role Membership

Endpoints

Full-text Indexes

Full-text Catalogs

Linked Servers

Linked Server Logins

Event Sessions

Server Audit Specifications

Full-text Stop Lists

SQL Server 2005

Server Role Membership

Endpoints

Full-text Indexes

Full-text Catalogs

Linked Servers

Linked Server Logins

Typically, these objects must be outside the deployment transaction because they are maintained by using system stored procedures.

Considerations for Deploying Changes to an Existing Database

When you deploy changes to an existing database, some changes could cause data loss. If a change might cause data in a table to be lost, the deployment will be canceled if the Block incremental deployment if data loss might occur check box is selected. By default, this check box is selected, but you can find it in the Properties window for your project. For more information, see An Overview of Database Project Settings.

The following kinds of changes will cause data loss: if a table is dropped and recreated, if you change the size of a column (char(100) to char(50) or nchar(100) to char(100)), or if you change the collation of a column that has a character type.

Note

When you use refactoring to rename a database object or to move a database object to another schema, the refactoring log file records that action. At deployment time, the information in the log file helps preserve the intent of your changes. For example, you might lose data if you renamed a table, because the table would be dropped and a table would be created with the new name. With the refactoring log file, the deployment script can instead rename the table, preserving your intent and your data.

Recovering from Failed Deployments

You can lose data if your deployment fails outside of the transacted sections of the deployment script. For this reason, you should strongly consider putting a shared database in single-user mode and backing it up before you deploy it.

Excluded Files

If you exclude files from your database project, the database objects that are defined within those files will not be included in the .dbschema file that is created when the database project is built. Those objects are not deployed because the objects are not included in the .dbschema file. If you are still working on one or more objects but want to deploy work that is already complete, you can exclude files from the build so only objects that are ready are included in the .dbschema file and deployed to the target database. You can later include the files when they are ready to be deployed, and deployment will update the database with the new objects without modifying the existing objects (if they have not been changed in your project). For more information, see How to: Exclude Files from a Database Project.

Important

You can lose data if you exclude objects from your database project that exist in the target database and then deploy the project. Those objects will be deleted from the target database if the Generate DROP statements for objects that are in the target database but that are not in the database project check box is selected in the deployment configuration.

Server Projects

Server projects contain definitions for objects in the "master" database. The name of the target database for a server project is always "master". For each server setting, you can specify whether you want to verify the value of that setting when you deploy the server project. Settings that you do not verify are ignored. If the value of a server setting does not match the value of a setting that you want to verify, deployment fails with an error message.

Command-Line Builds

In addition to performing the build, deploy, or clean actions from within the Visual Studio user interface, you can also perform these actions at a command prompt by using MSBuild.exe. You can specify Build, Deploy, Rebuild, and Clean targets. By default, the build and deploy processes use the project properties that are defined within the database project (in the .dbproj file or the .dbproj.user file). However, you can override these properties at a command prompt or from within a response file.

Important

You should close Visual Studio before you perform a command-line build. If you perform a command-line build when Visual Studio is running, some errors might not be caught.

You can also use VSDBCMD.EXE to deploy a .dbschema file from a command prompt. You can use VSDBCMD on a computer that does not have Visual Studio installed. For more information, see How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE. When you deploy to a production environment, you can use VSDBCMD or you can generate a deployment script and then manually deploy that script by using the Transact-SQL Editor or another tool such as SQL Server Management Studio.

Command-Line Syntax

You can build the database project at a command prompt by using the following examples of syntax:

MSBuild /target:Build MySolutionName.sln
This example performs the build action on the solution that is named MySolutionName.sln by using the project properties that are specified in the project files that the solution contains. If the solution contains multiple database projects, they are built together with everything else in the solution. You can also build a specific database project. The Build target includes pre-deployment and post-deployment scripts in the generated build script.
MSBuild /target:Deploy /p:UseSandboxSettings=false /p:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=(local)\SQLEXPRESS;Integrated Security=True;Pooling=False" MyProjectName.dbproj
This example demonstrates how to deploy the database project while overriding the target database name and connection string.
MSBuild /target:Deploy /p:UseSandboxSettings=false /p:DeploymentConfiguration=DeployPath\AlternateDeploymentConfiguration.deploymentconfig /p:SqlCommandVarsFile=VarPath\AlternateVariables.sqlcmdvars /p:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=(local)\SQLEXPRESS;Integrated Security=True;Pooling=False" MyProjectName.dbproj
This example demonstrates how to deploy the database project while specifying a different target database and connection string and overriding the deployment configuration and deployment variables.
MSBuild /target:Deploy /p:UseSandboxSettings=false /p:BuildScriptName=MyScriptName.sql /p:outdir=BuildScriptPath /p:TargetDatabase=UpdatedTargetDatabase;TargetConnectionString="Data Source=InstanceName\DatabaseName;Integrated Security=True;Pooling=False" ProjectPath\MyProjectName.dbproj
This example demonstrates how to deploy a database from a computer other than the one on which the build occurred. For example, you might use this syntax if you have a central build computer that creates a build script every night. You must specify the name of the build script, the path where the build script can be found (outdir), the target database, and the path and file name of the database project.
MSBuild @dbbuild.arf MyProjectName.dbproj
This example demonstrates how to use a response file to provide command-line arguments. The file, dbbuild.arf, can contain any valid arguments for MSBuild, including those that override project properties.
MSBuild /target:Rebuild MyProjectName.dbproj
This example rebuilds the specified project or solution, even if it has not changed since the last time it was built.
MSBuild /target:Clean MyProjectName.dbproj
This example demonstrates how to delete any existing build scripts. In most cases, you would follow this action with another build or deploy action.

Note

You can abbreviate /target: as /t: and /property: as /p:.

For more information, see MSBuild Command Line Reference.

For more information about response files, see this topic on the Microsoft Web site: MSBuild Response Files.

Note

To run MSBuild.exe, you must either use the Visual Studio Command Prompt, or you must run the vsvars32.bat batch file. You can find this batch file in the folder that the %VS80COMNTOOLS% environment variable specifies.

Required Software on the Build Computer

Visual Studio Team Foundation Server 2010 has native support for building, deploying, unit testing, and generating data for a database project. You do not have to install Visual Studio on your build computer. If you are not using Visual Studio Team Foundation Server 2010 on your build computer, you must install Visual Studio 2010 Professional, Visual Studio 2010 Premium, or Visual Studio 2010 Ultimate on your build computer.

In addition, if you want to deploy a database from Team Foundation Build other than as part of a database unit test, you must perform an additional installation. You must copy the %PROGRAM FILES%\Microsoft Visual Studio 10.0\VSTSDB\Deploy folder and its contents, including subfolders, from a Visual Studio 2010 installation to your build computer. For more information, see How to: Deploy Changes using Team Foundation Build and Walkthrough: Define a Custom Workflow to Deploy a Database from Team Foundation Build.

Project Properties

Some properties of database and server projects affect how those projects will be built and deployed. These properties are stored within the project file and the .user file, but you can override them at a command prompt or in a response file. For more information, see How to: Configure Build Settings for Database and Server Projects and How to: Configure Deployment Settings for Database and Server Projects.

Overriding Deployment Manifest Properties

You can override the default deployment properties (such as deployment configuration, connection string, or SQLCMD variables) when you deploy from the command line. You might choose to do this if you want to deploy a .dbschema file into multiple environments.

For example, if you want to deploy a schema defined in EnterpriseDB.dbproj into development, test, and production environments, you could use the following command lines:

Deployment Environment

MSBuild EnterpriseDB.dbproj /t:Deploy /p:UseSandboxSettings=false /p:DeploymentConfigurationFile=sql\debug\Development.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Development.sqlcmdvars /p:TargetConnectionString="Data Source=DEV\sql2008;Integrated Security=true;Pooling=false"

Test Environment

MSBuild EnterpriseDB.dbproj /t:Deploy /p:UseSandboxSettings=false /p:DeploymentConfigurationFile=sql\debug\UserTest.sqldeployment /p:SqlCommandVariablesFile=sql\debug\UserTest.sqlcmdvars /p:TargetConnectionString="Data Source=USERTEST\sql2008;Integrated Security=true;Pooling=false"

Production Environment

MSBuild EnterpriseDB.dbproj /t:Deploy /p:UseSandboxSettings=false /p:DeploymentConfigurationFile=sql\debug\Production.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Production.sqlcmdvars /p:TargetConnectionString="Data Source=PRODUCTION\sql2008;Integrated Security=true;Pooling=false"

In each environment, you provide a different deployment configuration file, a different SQLCMD variables file, and a different connection string.

An Overview of Database Build and Deployment

Deployment Scripts

Deployment Manifest

Transactions in the Deployment Script

Considerations for Deploying Changes to an Existing Database

Recovering from Failed Deployments

Excluded Files

Server Projects

Command-Line Builds

Command-Line Syntax

Required Software on the Build Computer

Project Properties

Overriding Deployment Manifest Properties

See Also

Tasks

Concepts

Additional resources