An Overview of Database Project Settings

Article
02/04/2013

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
References
Build
Deploy

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

Note

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

Project Settings

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

Build Events Settings

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

References

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.

Build Settings

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

Deploy

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

An Overview of Database Project Settings

Project Settings

Build Events Settings

References

Build Settings

Deploy

Deployment Configuration File

See Also

Tasks

Concepts

Additional resources