With the release of DBPro GDR there is now a command line deployment tool call VSDBCMD.exe.  This tool can be used to import a database schema into a .dbschema file or deploy a database from an existing .dbschema.  A .dbschema file can also be created by building a database project within visual studio.  When trying to automate the building and deploying of these database projects you have a choice to use msbuild or VSDBCMD.exe.  I will show an example of using both, but first lets take a look out how to use the VSDBCMD.exe command line.

In a typical install, VSDBCMD.exe should be located in C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy.  If you plan on running this utility on a machine that doesn’t have DBPro installed on it, all you need to do is copy the Deploy directory to the machine.  If you plan on using this utility in your build process, what I do is check this folder into source control this way when the build server goes to get the latest version of code it brings down this folder and then I reference VSDBCMD.exe from there.  Or you can install DBPro on the team foundation build servers. 

VSDBCMD Example

Here is an example of using VSDBCMD to generate a deployment script:

• /ManifestFile – Specifies the deployment manifest file to use when you deploy the database. The deployment manifest is created in the build output path when you build your database project.  The deployment properties passed into the command will override any properties set in this file.
• /a – Specifies that we want to deploy a .dbchema file.  To import set this option to Import.
• /cs – Connection String to the target database.
• /p – Deployment Properties - These properties can also be defined in the .sqldeployment file in the database solution and can be specified using the /p:DeploymentConfigurationFile property.
  • AlwaysCreateNewDatabase
  • BlockIncrementalDeploymentIfDataLoss
  • CommentOutSetVarDeclarations
  • DeployDatabaseProperties
  • DeploymentCollationPreference
  • EnforceMinimalDependencies
  • GenerateDeployStateChecks
  • GenerateDropsIfNotInProject
  • IgnoreAnsiNulls
  • IgnoreAutoGeneratedNames
  • IgnoreCollations
  • IgnoreColumnCollation
  • IgnoreComments
  • IgnoreDmlTriggerOrder
  • IgnoreDmlTriggerState
  • IgnoreExtendedProperties
  • IgnoreFileAndLogFilePath
  • IgnoreFullTextCatalogFilePath
  • IgnoreIdentitySeed
  • IgnoreIncrement
  • IgnoreIndexOptions
  • IgnoreLockHintsOnIndexes
  • IgnoreLoginSids
  • IgnorePermissions
  • IgnoreQuotedIdentifiers
  • IgnoreRoleMembership
  • IgnoreTableOptions
  • IgnoreUserSettingsObjects
  • IgnoreWithNocheckOnCheckConstraints
  • IgnoreWithNocheckOnForeignKeys
  • IncludeTransactionalScripts
  • PerformDatabaseBackup
  • SingleUserMode
  • TreatVerificationErrorsAsWarnings
• /dd – Setting this value to + will generate a script and then deploy it to the target database.  Setting it to – will just generate the script.

The same command listed above can be used to deploy the script by changing the /dd option:

Here is a complete VSDBCMD Command-Line Reference

Using VSDBCMD in a MSBuild Script

Using VSDBCMD in a TFS build script would look something like the code shown below.  Basically, in this scenario I’m calling VSDBCMD to generate a script and then copying that script into the drop folder.  I could have also deployed the changes by setting /dd to +.

Building And Deploying DBPro Projects With MSBuild

If you rather use MSBuild to build and deploy your DBPro projects here is an example of what that MSBuild code would look like.  In this script I’m setting DeployToDatabase to true so that is pushes the changes to the target database.