Export (0) Print
Expand All

An Overview of Database Project Settings

You use database project settings to control aspects of your database and build configurations. These settings fall into the following categories:

Project settings, build events, and deployment properties are stored in the database project and shared through version control.

NoteNote

User-specific settings are stored in the .dbproj.user file. Project-specific settings are stored in the .dbproj file.

The settings in the following table apply to all configurations of this database project.

Field

Default value

Description

Project version

The version of SQL Server that you specified when you created the database project.

Specifies the version of SQL Server that you are targeting with this database project.

Collation for database model

English (United States) (1033) - CI

Specifies the default rules that by which data is sorted and compared in the underlying SQL Server Compact Edition database that stores the model of your database. You should specify a value for this setting that is as close to your target database collation as possible. You might want to differ by case. For example, you could use a case-insensitive collation for the model and a case-sensitive collation for your database. You can override the default collation within object definitions.

NoteNote
The default collation is the same for all platforms. The default value does not change to match the current locale. You must manually set the default collation to the appropriate collation for your locale.

Default schema

dbo

Specifies the default schema in which objects are created. You can override this setting when you change one or more of the object definitions.

Include schema name in file name

Yes

Specifies whether file names include the schema as a prefix, for example, dbo.Products.table.sql. If this check box is cleared, file names for objects take the form ObjectName.ObjectType.sql

Catalog properties file

Properties\CatalogProperties.catalogproperties

Contains detailed property settings for the target database. All the properties match the properties of a SQL Server database. For more information, see this topic on the Microsoft Web site: Database Properties (Options Page).

You can use these settings to specify a command line to execute before the build operation starts and a command line to execute after the build operation is completed.

Field

Default value

Description

Pre-build event command line

None

Specifies the command line to execute before the project is built. Click Edit Pre-build to modify the command line.

Post-build event command line

None

Specifies the command line to execute after the project is built. Click Edit Post-build to modify the command line.

Run the post-build event

On successful build

Specifies whether the post-build command line should be run always, only if the build was successful, or only when the build updated the project output (the build script).

For more information, see How to: Specify Custom Actions That Run Pre-build or Post-build and Pre-build Event / Post-build Event Command Line Dialog Box (Database Projects).

You can use this page to define the server and database variables that are associated with a cross-database reference. In addition, you can specify the values of those variables. For more information, see Using References in Database Projects.

You can use these settings to affect the build script and the target database. These settings are specific to the configuration and platform that you specify, and they typically vary from user to user.

Field

Default value

Description

Build output path

.\sql\

Specifies where the build script will be generated when you build or deploy the database project. If you specify a relative path, you must specify it relative to the database project path. If the path does not exist, it is created.

Build output file name

DatabaseProjectName.sql

Specifies the name that you want to give the script that is generated when you build the database project. If you selected the Automatically generate the build output file name check box, the file name that is automatically generated overwrites any value that you specify in this field.

Treat warnings as errors

No

Specifies whether a warning should cause the build and deployment process to be canceled. If this check box is cleared, warnings appear, but the build and deployment process continue.

This setting is specific to the project, not the user, and is stored in the .dbproj file.

Suppress Warnings

Blank

Specifies a list of numbers, delimited by commas or semi-colons, that identify warnings that are suppressed. Suppressed warnings do not appear in the Error List window, and they do not affect the build success, even if you select the Treat warnings as errors check box.

NoteNote
You can also suppress a type of warning for a specific file if you do not want to suppress that type throughout the database project. For more information, see How to: Suppress One or More Types of Warnings.

You can use these settings to control the deployment of your database project.

Field

Default value

Description

Configure deployment settings for

My project settings

Specifies whether you want to modify deployment settings for the project that is shared with other developers or you want to modify settings for your isolated development environment only.

Deploy action

Create a deployment script (.sql) and deploy to database

Specifies whether you want deploy the created .sql script to the target server or create the script without deploying it.

Deployment script name

ProjectName.sql

Specifies the name that you want to use for the deployment script.

Target connection

blank

Specifies the connection information for the database server that you want to target for the specified build configuration.

Target database name

DatabaseProjectName

Specifies the name of the database to be created or updated through the connection that you specify in the Target connection field.

Deployment Configuration File

The deployment configuration file contains details that are specific to each deployment target, as the following table describes.

Field

Default value

Description

Deployment comparison collation

Use the collation of my project.

Specifies what collation to use when comparing the source and target models during deployment. If you click Use the collation of my project, the source collation is used. If you click Use the collation of the server, then the target collation is used.

Deploy database properties

Yes

Specifies whether the CatalogProperties.catalogproperties settings are deployed when you deploy the database project.

Always re-create database

No

Specifies whether the database will be dropped and re-created instead of performing an incremental deployment. You might want to select this check box if you want to run database unit tests against a clean deployment of the database, for example. If this check box is cleared, the existing database will be updated instead of dropped and re-created.

Block incremental deployment if data loss might occur

Yes

Specifies whether deployment will stop if an update will cause data loss. If this check box is selected, changes that would create data loss cause deployment to stop with an error, which keeps data from being lost. For example, deployment would stop if a varchar(50) column were changed to varchar(30).

NoteNote
Deployment is blocked only if the tables where data loss might occur contain data. Deployment continues if no data would be lost.

Execute deployment script in single-user mode

No

Specifies whether the database should be put in single-user mode during deployment. If you are deploying to a shared database server, you should put the database in single-user mode when you deploy database changes. This step prevents other users from making changes while you deploy the project. This option is unavailable if you have enabled full-text search on the Project Settings tab of the properties for the database project.

Important noteImportant
When the database is in single-user mode, all other existing connections to the database are dropped as soon as you deploy changes to the database. The ROLLBACK IMMEDIATE clause is specified so that pending transactions are terminated as soon as the database is put into single-user mode.

Back up database before deployment

No

Specifies whether the database should be backed up before you deploy it. If this check box is cleared, a backup is not performed automatically. If the check box is selected, statements are added to the pre-deployment script to back up your database. A backup operation can be slow. If you are building and deploying to an isolated development environment, you might decide not to back up the database before you build and deploy it.

Important noteImportant
Before you deploy to a production server, you should always back up the database. If you do not back up the database automatically as part of the build and deployment process, you should manually back it up before you deploy changes.

Generate DROP statements for objects that are in the target database but that are not in the database project

No

Specifies whether objects that are in the target database but not in the database project should be dropped as part of the deployment script. You can exclude some files in your project to temporarily remove them from your build script. However, you might want to leave the existing versions of those objects in the target database. This check box has no effect if the Always re-create database check box is selected, because the database will be dropped.

Do not use ALTER ASSEMBLY statements to update CLR types

No

Specifies whether ALTER ASSEMBLY statements are used to update common language run-time (CLR) types or whether the object that instantiates the CLR type will instead be dropped and re-created when you deploy changes.

The following table describes the advanced deployment settings.

Field

Default value

Description

AbortOnFirstError

True

Specifies whether deployment should be canceled when the first error occurs.

BuildtimeContributorsMustExist

True

Specifies whether deployment contributors (that were registered when the database project was build) are required to be registered when the project is deployed.

CheckNewConstraints

True

When constraints are created or re-created, specifies whether, 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 (due to 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.

CommentOutSetVarDeclarations

False

Specifies whether the declaration of SetVar variables should be commented out in the generated deployment script. You might choose to do this if you plan to specify the values on the command line when you deploy by using a tool such as SQLCMD.EXE.

DisableAndReenableDdlTriggers

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.

DropConstraintsNotInSource

True

Specifies whether constraints that do not exist in your database project will be dropped from the target database when you deploy updates to a database.

DropIndexesNotInSource

True

Specifies whether indexes that do not exist in your database project will be dropped from the target database when you deploy updates to a database.

EnforceMinimalDependencies

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.

GenerateDeployStateChecks

True

Specifies whether statements are generated in the deployment script to verify that the database name and server name match the names specified in the database project.

IgnoreAnsiNulls

False

Specifies whether differences in the ANSI Nulls setting should be ignored or updated when you deploy updates to a database.

IgnoreAuthorizer

False

Specifies whether differences in the Authorizer should be ignored or updated when you deploy updates to a database.

IgnoreColumnCollation

False

Specifies whether differences in the column collations should be ignored or updated when you deploy updates to a database.

IgnoreComments

False

Specifies whether differences in the comments should be ignored or updated when you deploy updates to a database.

IgnoreCryptographicProviderFilePath

False

Specifies whether differences in the file path for the cryptographic provider should be ignored or updated when you deploy updates to a database.

IgnoreDdlTriggerOrder

False

Specifies whether differences in the order of Data Definition Language (DDL) triggers should be ignored or updated when you deploy updates to a database or server.

IgnoreDdlTriggerState

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.

IgnoreDefaultSchema

False

Specifies whether the default schema setting for the user should be updated, if it is different, when you deploy updates to a database.

IgnoreDmlTriggerOrder

False

Specifies whether differences the order of Data Manipulation Language (DML) triggers should be ignored or updated when you deploy updates to a database.

IgnoreDmlTriggerState

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.

IgnoreExtendedProperties

False

Specifies whether differences in the extended properties should be ignored or updated when you deploy updates to a database.

IgnoreFilegroupPlacement

True

Specifies whether differences in the placement of objects in filegroups should be ignored or updated when you deploy updates to a database.

IgnoreFillFactor

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.

IgnoreIdentitySeed

False

Specifies whether differences in the seed for an identity column should be ignored or updated when you deploy updates to a database.

IgnoreIncrement

False

Specifies whether differences in the increment for an identity column should be ignored or updated when you deploy updates to a database.

IgnoreIndexOptions

False

Specifies whether differences in the index options should be ignored or updated when you deploy updates to a database.

IgnoreIndexPadding

True

Specifies whether differences in the index padding should be ignored or updated when you deploy updates to a database.

IgnoreKeywordCasing

True

Specifies whether differences in the casing of keywords should be ignored or updated when you deploy updates to a database.

IgnoreLockHintsOnIndexes

False

Specifies whether differences in the lock hints on indexes should be ignored or updated when you deploy updates to a database.

IgnoreLoginSids

False

Specifies whether differences in the security identification number (SID) should be ignored or updated when you deploy updates to a database.

IgnoreNotForReplication

False

Specifies whether differences in the NOT FOR REPLICATION setting should be ignored or updated when you deploy updates to a database.

IgnoreObjectPlacementOnPartitionScheme

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.

IgnorePartitionSchemes

False

Specifies whether differences in partition schemes and functions should be ignored or updated when you deploy updates to a database.

IgnorePermissions

False

Specifies whether differences in the permissions should be ignored or updated when you deploy updates to a database.

IgnoreQuotedIdentifiers

False

Specifies whether differences in the quoted identifiers setting should be ignored or updated when you deploy changes to a database.

IgnoreRoleMembership

False

Specifies whether differences in the role membership of logins should be ignored or updated when you deploy updates to a database.

IgnoreRouteLifetime

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.

IgnoreSemicolonBetweenStatements

True

Specifies whether differences in the semi-colons between Transact-SQL statements will be ignored or updated when you deploy updates to a database.

IgnoreStatisticsSample

True

Specifies whether differences in the sample used for CREATE STATISTICS will be ignored or updated when you deploy updates to a database.

IgnoreTableOptions

False

Specifies whether differences in the table options will be ignored or updated when you deploy updates to a database.

IgnoreUserSettingsObjects

False

Specifies whether differences in the user settings objects will be ignored or updated when you deploy updates to a database.

IgnoreWhitespace

True

Specifies whether differences in white space will be ignored or updated when you deploy updates to a database.

IgnoreWithNocheckOnCheckContraints

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.

IgnoreWithNocheckOnForeignKeys

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.

IncludeTransactionalScripts

False

Specifies whether transactional statements should be used where possible when you deploy to a database.

ScriptDatabaseCollation

True

Specifies whether differences in the database collation should be ignored or updated when you deploy updates to a database.

TreatVerificationErrorsAsWarnings

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.

UnmodifiableObjectWarnings

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.

VerifyDeployment

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.

Community Additions

ADD
Show:
© 2014 Microsoft