Udostępnij za pośrednictwem


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.

NoteNote
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:

  1. At design-time, in Database.sqlcmdvars, pointed to by the .deployment manifest

  2. On the command-line by specifying the /p:SqlCommandVariablesFile=Filepath

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

NoteNote
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

An Overview of Database Project Settings