An Overview of Database Project Settings
Database project settings control aspects of the database and your build configurations. These settings fall into the following categories:
Project Settings
Build Events
Database Properties
Filegroups
References
Build
Variables
Project settings, build events, and database 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
These settings 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 Microsoft SQL Server that you are targeting with this database project. |
Default schema |
dbo |
Specifies the default schema in which objects that are added to the database are created. You can override this setting when you change one or more of the object definitions. |
Include schema name in filename |
Yes |
Specifies whether file names include the schema as a prefix (for example, dbo.Products.table.sql) or whether the file names are simply ObjectName.ObjectType.sql |
Enable full-text search |
No |
Specifies whether full-text search is enabled for this database project. Important If you select this check box, you cannot also set the check box to put the database in Single User mode during deployment. |
Enable SQLCLR integration |
No |
Specifies whether SQLCLR integration is enabled for the database that is associated with this database project. |
Default collation |
SQL_Latin1_General_CP1_CS_AS |
Specifies the default rules that are used to sort and compare data in this database project. 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. |
Note
If you change the collation of the project, a dialog box appears requiring you to confirm the change because the project must be unloaded and reloaded to change the default collation. Click Yes in response to the prompt to unload and reload a project.
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 about how to edit the command lines, see How to: Specify a Pre-build or Post-build Command Line and Pre-build Event / Post-build Event Command Line Dialog Box (Team Edition for Database Professionals).
Database Properties
You can use this page to configure the database properties. All of the properties correspond to properties of a SQL Server database. For more information about these properties, see Database Properties (Options Page).
Filegroups
You can use this page to modify the definitions of filegroups, files, and log files for the database project. For more information, see Overview of Files and Filegroups. These definitions typically reference variables that are defined on the Variables tab.
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 Overview of Cross-Database References.
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, it is relative to the database project path. If the path does not exist, it is created. |
Target connection |
blank |
Specifies the connection information for the database server that you want to target for the selected build configuration. If you do not specify a target connection, the SQL Server 2005 instance that you specified as your design-time validation database will be used. |
Target database name |
DatabaseProjectName |
Specifies the name of the database to be created or updated at the connection that you specify in the Target Connection field. |
Default location for target database files |
The default location is retrieved from the target server. |
Specifies the default location where database files should be created on the target server. You can retrieve the default location for that server if you click Refresh. |
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 updated to match the database project. If you click Do not script the collation, neither collation will be changed, but deployment might fail. |
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 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, such as changing a varchar(50) column to be varchar(30), cause deployment to stop with an error, keeping you from losing data. Note Deployment is blocked only if the tables where data loss might occur contain data. Deployment is not blocked if there is no data to be lost. |
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. |
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 automatically performed. If the check box is selected, statements are added to the pre-deployment script to back up your database. Note A backup operation can be slow. If you are building and deploying to an isolated development environment and not to a shared test, staging, or production server, you might decide not to back up the database as part of building and deploying it. Important Before you deploy to a production server, you should always back up the database. If you do not automatically back up the database as part of the build and deployment process, you should manually back up the database 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 to prevent other users from making changes to the database when you deploy database changes. 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 immediately when the database is put into single user mode. Note This option is disabled if you have enabled full-text search on the Project Settings tab of the database project properties. |
Perform 'smart' column name matching when you add or rename a column |
No |
Specifies whether to apply a heuristic when you deploy updates to determine when to rename a column instead of performing a DROP and an ADD operation. The heuristic is based on the properties of the column and the names of the source and target columns. This check box has no effect if the Always re-create database check box is selected because the database will be dropped and re-created. |
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. If you exclude some files in your project to temporarily remove them from your build script, 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 runtime (CLR) types or whether the object that instantiates the CLR type will instead be dropped and re-created when you deploy changes. |
Suppress Warnings |
Blank |
Specifies a list, delimited by commas or semi-colons, of warning numbers 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. |
Variables
You can use this page to define variables and their values that you can use in either the definitions for filegroups and files or in the pre- and post-deployment scripts. These settings are specific to the configuration and platform that you specify.
| Field | Default value | Description |
|---|---|---|
Variable Name |
Blank |
The name of a variable that you can use in a filegroup or file definition or in the pre-deployment or post-deployment scripts. When you reference the variable name from a script or in an object definition, you must use the syntax: [$(VariableName)]. |
Variable Value |
Blank |
The value that you want to associate with the variable for the current configuration and platform. |
See Also
Tasks
How to: Configure Database Projects for Build and Deployment
How to: Prepare Database Build Scripts
How to: Deploy Changes to New or Existing Databases
Walkthrough: Create and Deploy a New Version-controlled Database
Walkthrough: Deploy Changes to an Existing Version-controlled Database
Concepts
An Overview of Database Build and Deployment
Terminology Overview of Team Edition for Database Professionals
Other Resources
How to: Suppress One or More Types of Warnings
How to: Define Variables for Database Projects