Export (0) Print
Expand All

An Overview of Database Project Settings

Updated: June 2010

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. User-specific settings are stored in the .dbproj.user file. Each user can specify deployment settings for the project or for only their local development environment. For more information, see Deploy.

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.

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\Database.sqlsettings

Contains detailed property settings for the target database. All of 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 another 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 Edition).

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 to be canceled. If this check box is cleared, warnings appear, but the build and deployment 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

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.

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.

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 collation default

Blank

Specifies what collation to use during deployment if the target database collation does not match the database project collation. If you click Use the collation of the server, the database project will be changed to match the target database. If you click Use the collation of the database project, the target database will be changed to match the database project. If you click Do not script the collation, neither collation will be changed, but deployment might fail.

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.

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 Note:
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.

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 Note:
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.

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 could 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 differences in the default schema should be ignored or updated 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.

Date

History

Reason

June 2010

Added section to cover advanced properties to address feedback on the content Wiki.

Also updated the name of the Catalog properties file and removed two obsolete properties and their descriptions.

Customer feedback.

Community Additions

ADD
Show:
© 2014 Microsoft