|Important||This document may not represent best practices for current development, links to downloads and other resources may no longer be valid. Current recommended version can be found here. ArchiveDisclaimer|
An Overview of Database Build and Deployment
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 it exists), and a deployment script is generated. Depending on your settings, the script is deployed to the target database.
As an alternative, you can generate the deployment script and then modify it before you deploy it to a staging or production server. The Clean build action deletes any existing build artifacts, such as scripts, deployment manifests, and the .dbschema files.
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 Scenario: Create and Modify Database Scripts.
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:
<Project ToolsVersion="3.5" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <PropertyGroup> <TargetDatabase>Empty2008DbProj</TargetDatabase> <DeployToDatabase>False</DeployToDatabase> <DeployToScript>True</DeployToScript> <SourceModel>Empty2008DbProj.dbschema</SourceModel> <DeployScriptFileName>Empty2008DbProj.sql</DeployScriptFileName> <DeploymentConfigurationFile>Database.sqldeployment</DeploymentConfigurationFile> </PropertyGroup> <ItemGroup> <DeploymentExtensionConfiguration Include="Script.PostDeployment.sql"> <__PostdeploymentMetadata> </__PostdeploymentMetadata> </DeploymentExtensionConfiguration> <DeploymentExtensionConfiguration Include="Script.PreDeployment.sql"> <__PredeploymentMetadata> </__PredeploymentMetadata> </DeploymentExtensionConfiguration> </ItemGroup> <ItemGroup> <Reference Include="Microsoft.SqlTypes.dbschema" /> </ItemGroup> <PropertyGroup> <SqlCommandVariablesFile>Database.sqlcmdvars</SqlCommandVariablesFile> </PropertyGroup> </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.
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
Linked Server Logins
Server Audit Specifications
Full-text Stop Lists
Microsoft SQL Server 2005
Server Role Membership
Linked Server Logins
SQL Server 2000
Server Role Membership
Linked Server Logins
Typically, these objects must be outside the deployment transaction because they are maintained by using system stored procedures.
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.
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.
Visual Studio Team System Database Edition creates a save point in the transaction log so that you can revert to that save point if deployment fails. However, you can lose data if the data in the database is modified during deployment between when the save point was created and when the database was reverted. For this reason, you should strongly consider putting a shared database in single-user mode and backing it up before you deploy it.
You must use the full recovery model to restore a database from a failed deployment. The simple recovery model is not sufficient because the transaction log is required. For more information, see this topic on the Microsoft Web site: Recovery Model Overview.
If you exclude files from your database project, the database objects that are defined within those files will not be included in your build or deployment. If you are still working on one or more objects but want to deploy work that is already complete, you can exclude files to deploy only those items that are ready. 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.
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 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.
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, Clean, SQLBuild, SQLDeploy, and CleanProject 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.
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 build the database project at a command prompt by using the following examples of syntax:
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.
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.
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:
MSBuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\Development.sqldeployment /p:SqlCommandVariablesFile=sql\debug\Development.sqlcmdvars /p:TargetConnectionString="Data Source=DEV\sql2008;Integrated Security=true;Pooling=false"
MSBuild EnterpriseDB.dbproj /t:Deploy /p:DeploymentConfigurationFile=sql\debug\UserTest.sqldeployment /p:SqlCommandVariablesFile=sql\debug\UserTest.sqlcmdvars /p:TargetConnectionString="Data Source=USERTEST\sql2008;Integrated Security=true;Pooling=false"
MSBuild EnterpriseDB.dbproj /t:Deploy /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.