Command-Line Reference for VSDBCMD.EXE (Deployment and Schema Import)
You can use VSDBCMD.EXE to:
Import a database schema from a live database into a .dbschema file
Generate a deployment script from a .dbschema file
Generate a deployment script from a .dbschema file and deploy that script to a target database
Generate a deployment script by comparing two .dbschema files.
In addition, you can specify options to customize how your database is deployed or imported.
For more information about how you can use this tool, see How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE and How to: Import a Schema from a Command Prompt.
Important
You cannot use VSDBCMD.EXE to deploy a data-tier application component (DAC) project or to import a schema into a DAC project.
General Options
The options in the following table apply to both the Deploy and Import actions.
Option |
Short Form |
Required? |
Notes |
|---|---|---|---|
/Action:{Import|Deploy} |
/a:{Import|Deploy} |
Yes |
Specifies whether you want to import objects and settings into a .dbschema file or whether you want to deploy a .dbschema file to a target server or database. |
/ConnectionString:"String" |
/cs:"String" |
No |
Specifies the connection string to the target database. For example, you might specify /cs:"Data Source=MyDatabase/SQL2K8;Integrated Security=True;Pooling=False;Initial Catalog=AdventureWorks2008". To import a schema, you must specify the database name by using Initial Catalog. For more information about the syntax for a connection string, see Connection String Syntax (ADO.NET). |
/DatabaseSchemaProvider:DspName |
/dsp:DspName |
Yes |
Specifies the type of database schema provider to which you are connecting. For example, use /dsp:SQL for SQL Server. |
@File |
Not available |
No |
You can specify a response file that contains one or more command-line options. |
/Quiet[+|-] |
/q |
No |
Specifies whether you want to suppress detailed feedback from VSDBCMD. |
/DeployToDatabase[+|-] |
/dd |
No |
Specifies that you want to generate a deployment script and deploy it to the target database. If you do not specify this option, you generate a deployment script, but that script is not deployed. |
/ModelFile:FileName |
/model:FileName |
No |
Specifies the name of the .dbschema file that you want to deploy or that you want to create when you import a schema. This is not required if you specify a deployment manifest. |
/Properties:PropertyName=PropertyValue |
/p:PropertyName=PropertyValue |
No |
Specifies a property name and a value that you want to override at a command prompt. See Deployment Options and Import Options for properties that are specific to those actions. |
Deployment Options
The options in the following table apply only when you deploy a .dbschema file.
Option |
Short Form |
Required? |
Notes |
|---|---|---|---|
/ManifestFile:FileName |
/manifest:FileName |
No |
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. .gif "Note") Note
Deployment will fail if your deployment manifest file references an output script file that already exists and is read-only.
|
/DeploymentScriptFile:FileName |
/script:FileName |
No |
Specifies the name that you want to give to the deployment script. |
/ExtensionArguments |
/ext:String |
No |
Specifies name/value pairs for parameters that are passed to custom deployment contributors. |
/TargetModelFile: FileName |
/targetmodel: FileName |
No |
Specifies a target .dbschema file that you want to compare to the source .dbschema file. Use this option when you want to generate a deployment script by comparing two .dbschema files. For more information, see Compare Schemas by using VSDBCMD.EXE. |
Note
When you create an incremental deployment script, the source and target database names must match.
Overriding Individual SQLCMD Variables on the Command Line
You can use the following syntax to override the value for a single SQLCMD variable:
**/p:SqlCmdVariableName=**Value
If you specify a variable name that was not declared in the .sqlcmdvars file, an error will occur.
You can specify SQLCMD variables at three points, which are applied in the following order:
At design-time, in Database.sqlcmdvars, pointed to by the .deployment manifest
On the command-line by specifying the /p:SqlCommandVariablesFile=Filepath
On the command line by specifying a specific variable name and value
The second method overrides the first, and the third overrides the second and the first.
Common Deployment Properties
You can specify the properties in the following table when you deploy a .dbschema file at a command prompt.
Option |
Default Value |
Notes |
|---|---|---|
/p:AbortOnFirstError={True|False} |
True |
Specifies whether deployment should be canceled when the first error occurs. |
/p:AlwaysCreateNewDatabase={True|False} |
False |
Specifies whether the database should be updated or whether it should be dropped and re-created when you deploy changes. |
/p:AnsiNulls={True|False} |
Defaults to the value of this setting in the source model |
Specifies the ANSI_NULLS connection setting. This setting overrides the value in the model |
/p:AnsiPadding={True|False} |
Defaults to the value of this setting in the source model |
Specifies the ANSI_PADDING connection setting. This setting overrides the value in the model. |
/p:AnsiWarnings={True|False} |
Defaults to the value of this setting in the source model |
Specifies the ANSI_WARNINGS connection setting. This setting overrides the value in the model. |
/p:ArithAbort={True|False} |
Defaults to the value of this setting in the source model |
Specifies the ARITH_ABORT connection setting. This setting overrides the value in the model. |
/p:BlockIncrementalDeploymentIfDataLoss={True|False} |
True |
Specifies whether you want deployment to stop if data loss might occur. |
/p:CheckNewConstraints={True|False} |
True |
Specifies whether, when constraints are created or re-created, they will be created with the NOCHECK option on by default. At the end of the deployment script, a block of statements will be added that will check all of the constraints as one set. By setting this property to True, you can deploy your schema without encountering data errors that are caused by a check or foreign key constraint in the middle of the deployment process by deferring the data check to the end of your deployment script. |
/p:CollationPreference={UseSourceModelCollation | UseTargetModelCollation } |
UseSourceModelCollation |
Specifies the collation that is used when comparing the source and target models. |
/p:CommentOutSetVarDeclarations={True|False} |
False |
Specifies whether you want the SETVAR statements that specify values for variables to be commented out in the deployment script. You might set this property to True if you want to use SQLCMD to deploy the script and you want to specify values for those variables at the command prompt. |
/p:ConcatNullYieldsNull={True|False} |
Defaults to the value of this setting in the source model |
Specifies the CONCAT_NULL_YIELDS_NULL connection setting. This setting overrides the value in the model. |
/p:DeployDatabaseProperties={True|False} |
True |
Specifies whether the database properties should be set or updated as part of the deployment script. |
/p:DeploymentCollationPreference={UseSourceModelCollation|UseTargetModelCollation|DontChangeCollations} |
DontChangeCollations |
This setting dictates how the database collation is handled when during deployment. There are three possible values: UseSourceModelCollation indicates that the target database collation will be updated if it does not match the collation specified by the source. UseTargetModelCollation indicates that the target database (or server) collation should be used. DontChangeCollations indicates that the collation of the server should not be changed. |
/p:DeploymentConfigurationFile=fileName |
The .sqldeployment file that is specified in the .deploymanifest file. |
Specifies the .sqldeployment file that you want to use when you deploy. |
/p:DisableAndReenableDdlTriggers={True|False} |
True |
Specifies whether Data Definition Language (DDL) triggers are disabled at the beginning of the deployment process and re-enabled at the end of the deployment process. |
/p:DoNotUseAlterAssemblyStatementsToUpdateCLRTypes={True|False} |
False |
Specifies that deployment should always drop and re-create an assembly if there is a difference instead of issuing an ALTER ASSEMBLY statement. |
/p:DropConstraintsNotInSource={True|False} |
True |
Specifies whether constraints that do not exist in your database project will be dropped from the target database when you deploy updates to that database. |
/p:DropIndexesNotInSource={True|False} |
True |
Specifies whether indexes that do not exist in your database project will be dropped from the target database when you deploy updates to that database. |
/p:EnforceMinimalDependencies={True|False} |
False |
Specifies whether the bodies of procedures, scalar functions, and table-valued functions are parsed and interpreted. If you enable this setting, dependencies within the bodies are not identified, but changes to the definition of the object will be identified. By setting this option to True, you might improve performance, but the missed dependencies might cause problems when you deploy. |
/p:GenerateDatabaseOptions={True|False} |
True |
Specifies whether the deployment script should contain statements that set the database options when you deploy the database. |
/p:GenerateDeployStateChecks={True|False} |
True |
Specifies whether you want the deployment script to contain statements that verify the state of the target database before you deploy the database schema. |
/p:GenerateDropsIfNotInProject={True|False} |
False |
Specifies whether to drop the objects in the target database that do not exist in the source project when you deploy updates to that target database. |
/p:IgnoreAnsiNulls={True|False} |
False |
Specifies whether differences in the ANSI Nulls setting should be ignored or updated when you deploy updates to a database. |
/p:IgnoreAuthorizer={True|False} |
False |
Specifies whether differences in the Authorizer should be ignored or updated when you deploy updates to a database. |
/p:IgnoreColumnCollation={True|False} |
False |
Specifies whether differences in the column collations should be ignored or updated when you deploy updates to a database. |
/p:IgnoreComments={True|False} |
False |
Specifies whether differences in the comments should be ignored or updated when you deploy updates to a database. |
/p:IgnoreCryptographicProviderFilePath={True|False} |
False |
Specifies whether differences in the file path for the cryptographic provider should be ignored or updated when you deploy updates to a database. |
/p:IgnoreDdlTriggerState={True|False} |
False |
Specifies whether differences in the enabled or disabled state of Data Definition Language (DDL) triggers should be ignored or updated when you deploy updates to a database. |
/p:IgnoreDefaultSchema={True|False} |
False |
Specifies whether differences in the default schema should be ignored or updated when you deploy updates to a database. |
/p:IgnoreDmlTriggerOrder={True|False} |
False |
Specifies whether differences the order of Data Manipulation Language (DML) triggers should be ignored or updated when you deploy updates to a database. |
/p:IgnoreDmlTriggerState={True|False} |
False |
Specifies whether differences in the enabled or disabled state of DML triggers should be ignored or updated when you deploy updates to a database. |
/p:IgnoreExtendedProperties={True|False} |
False |
Specifies whether differences in the extended properties should be ignored or updated when you deploy updates to a database. |
/p:IgnoreFilegroupPlacement={True|False} |
True |
Specifies whether differences in the placement of objects in filegroups should be ignored or updated when you deploy updates to a database. |
/p:IgnoreFillFactor={True|False} |
True |
Specifies whether differences in the fill factor for index storage should be ignored or whether a warning should be issued when you deploy updates to a database. |
/p:IgnoreIdentitySeed={True|False} |
False |
Specifies whether differences in the seed for an identity column should be ignored or updated when you deploy updates to a database. |
/p:IgnoreIncrement={True|False} |
False |
Specifies whether differences in the increment for an identity column should be ignored or updated when you deploy updates to a database. |
/p:IgnoreIndexOptions={True|False} |
False |
Specifies whether differences in the index options should be ignored or updated when you deploy updates to a database. |
/p:IgnoreIndexPadding={True|False} |
True |
Specifies whether differences in the index padding should be ignored or updated when you deploy updates to a database. |
/p:IgnoreKeywordCasing={True|False} |
True |
Specifies whether differences in the casing of keywords should be ignored or updated when you deploy updates to a database. |
/p:IgnoreLockHintsOnIndexes={True|False} |
False |
Specifies whether differences in the lock hints on indexes should be ignored or updated when you deploy updates to a database. |
/p:IgnoreLoginSids={True|False} |
False |
Specifies whether differences in the security identification number (SID) should be ignored or updated when you deploy updates to a database. |
/p:IgnoreObjectPlacementOnPartitionScheme={True|False} |
True |
Specifies whether differences in the mapping of partitions for a partitioned table or index to filegroups should be ignored or updated when you deploy updates to a database. |
/p:IgnorePermissions={True|False} |
False |
Specifies whether differences in the permissions should be ignored or updated when you deploy updates to a database. |
/p:IgnoreQuotedIdentifiers={True|False} |
False |
Specifies whether differences in the quoted identifiers setting should be ignored or updated when you deploy changes to a database. |
/p:IgnoreRoleMembership={True|False} |
False |
Specifies whether differences in the role membership of logins should be ignored or updated when you deploy updates to a database. |
/p:IgnoreRouteLifetime={True|False} |
True |
Specifies whether differences in the amount of time that SQL Server retains the route in the routing table should be ignored or updated when you deploy updates to a database. |
/p:IgnoreSemicolonBetweenStatements={True|False} |
True |
Specifies whether differences in the semi-colons between Transact-SQL statements will be ignored or updated when you deploy updates to a database. |
/p:IgnoreStatisticsSample={True|False} |
True |
Specifies whether differences in the sample used for CREATE STATISTICS will be ignored or updated when you deploy updates to a database. |
/p:IgnoreTableOptions={True|False} |
False |
Specifies whether differences in the table options will be ignored or updated when you deploy updates to a database. |
/p:IgnoreUserSettingsObjects={True|False} |
False |
Specifies whether differences in the user settings objects will be ignored or updated when you deploy updates to a database. |
/p:IgnoreWhitespace={True|False} |
True |
Specifies whether differences in white space will be ignored or updated when you deploy updates to a database. |
/p:IgnoreWithNocheckOnCheckConstraints={True|False} |
False |
Specifies whether differences in the value of the WITH NOCHECK clause for check constraints will be ignored or updated when you deploy updates to a database. |
/p:IgnoreWithNocheckOnForeignKeys={True|False} |
False |
Specifies whether differences in the value of the WITH NOCHECK clause for foreign keys will be ignored or updated when you deploy updates to a database. |
/p:IncludeTransactionalScripts={True|False} |
False |
Specifies whether transactional statements should be used where possible when you deploy to a database. |
/p:NumericRoundAbort={True|False} |
Defaults to the value of this setting in the source model |
Specifies the NUMERIC_ROUNDABORT connection setting. This setting overrides the value in the model. |
/p:PerformDatabaseBackup={True|False} |
False |
Specifies whether the target database should be backed up prior to any changes. |
/p:QuotedIdentifier={True|False} |
Defaults to the value of this setting in the source model |
Specifies the QUOTED_IDENTIFIER connection setting. This setting overrides the value in the model. |
/p:SingleUserMode={True|False} |
False |
Specifies whether the database should be placed in single user mode during updates. |
/p:SqlCommandVariablesFile=FileName |
The .sqlcmdvars file that is specified in the .deploymanifest file. |
Specifies the .sqlcmdvars file that you want to use when you deploy. |
/p:TargetDatabase="String" |
Not available |
Specifies the name of the database to which you want to deploy. |
/p:TreatVerificationErrorsAsWarnings={True|False} |
False |
Specifies whether errors encountered during deployment verification should be treated as warnings. The check is performed against the generated deployment plan before the plan is executed against your target database. Plan verification detects problems such as the loss of target-only objects, such as indexes, that must be dropped to make a change. Verification will also detect situations where dependencies, such as a table or view, exist because of a reference to a composite project, but do not exist in the target database. You might choose to do this to get a complete list of all deployment issues, instead of having deployment stop on the first error. |
/p:UnmodifiableObjectWarnings={True|False} |
True |
Specifies whether warnings should be generated when differences are found in objects that cannot be modified, for example, if the file size or file paths were different for a file. |
/p:VerifyDeployment={True|False} |
True |
Specifies whether checks should be performed before deployment that will stop deployment if issues are present that might block successful deployment. For example, your deployment might stop if you have foreign keys on the target database that do not exist in the database project, and that will cause errors when you deploy. |
You can display a complete list of deployment properties by typing the following command at a command prompt:
VSDBCMD /? /a:Deploy /dsp:sql /cs:"ConnectionString"
Common Import Properties
The properties in the following table apply only when you import objects and settings into a .dbschema file.
Option |
Default |
Notes |
|---|---|---|
/p:IgnoreExtendedProperties={True|False} |
False |
Specifies whether extended properties should be ignored or imported. |
/p:IgnorePermissions={True|False} |
False |
Specifies whether permissions should be ignored or imported. Note
The default value for this setting differs from the default when you import by using the Import Schema Wizard.
|
You can display a complete list of import properties by typing the following command at a command prompt:
VSDBCMD /? /a:Import /dsp:sql /cs:"ConnectionString"
Sample Command Lines to Deploy to Multiple Environments
You can use VSDBCMD to deploy a .dbschema file into multiple target environments. In this example, you deploy EnterpriseDB.dbschema into development, testing, and production environments.
Development Environment
"%programfiles%\Microsoft Visual Studio 10.0\vstsdb\deploy\vsdbcmd"
/a:Deploy
/manifest:EnterpriseDB.deploymanifest
/p:DeploymentConfigurationFile=Development.sqldeployment
/p:SqlCommandVariablesFile=Development.sqlcmdvars
/cs:"Data Source=DEV\sql2008;Integrated Security=true"
Testing Environment
"%programfiles%\Microsoft Visual Studio 10.0\vstsdb\deploy\vsdbcmd"
/a:Deploy
/manifest:EnterpriseDB.deploymanifest
/p:DeploymentConfigurationFile=UserTest.sqldeployment
/p:SqlCommandVariablesFile=UserTest.sqlcmdvars
/cs:"Data Source=USERTEST\sql2008;Integrated Security=true"
Production Environment
"%programfiles%\Microsoft Visual Studio 10.0\vstsdb\deploy\vsdbcmd"
/a:Deploy
/manifest:EnterpriseDB.deploymanifest
/p:DeploymentConfigurationFile=Production.sqldeployment
/p:SqlCommandVariablesFile=Production.sqlcmdvars
/cs:"Data Source=PRODUCTION\sql2008;Integrated Security=true"
For each environment, you provide the deployment configuration, the SQLCMD variables file, and the connection string that are specific to the target environment.
All environments share the deployment manifest. The deployment manifest includes the name of the .dbschema file that you want to deploy.
See Also
Tasks
How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE
Concepts
Build and Deploy Databases to a Staging or Production Environment