<commit_before>---
layout: docs
title: Publishing SQL Server databases from SSDT packages
---

<!-- markdownlint-disable MD022 MD032 -->
# Publishing SQL Server databases from SSDT packages
{:.no_toc}

"SQL Database" (`SqlDatabase`) deployment provider allows incremental publishing of database changes from [SQL Server Data Tools](https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx) (SSDT) package (`.dacpac`) to a local SQL Server instance, remote SQL Server or Azure SQL database.

In this guide:

* Comment to trigger ToC generation
{:toc}
<!-- markdownlint-enable MD022 MD032 -->


## Provider settings

* **Artifact** (`artifact`) - `.dacpac` artifact file name or deployment name or regexp matching one of these.
* **Target SQL Server connection string** (`connection_string`) - SQL connection string to the target database. **Must include database name**. For example `server=(local)\SQLEXPRESS;database=test101;Integrated security=SSPI;`
* **Register as a Data-tier Application** (`register_data_tier_application`) - default is `false`. You can read about Data-tier applications on [MSDN](https://msdn.microsoft.com/en-us/library/ee210546.aspx) and also there is a nice introductory article on [CodeProject](http://www.codeproject.com/Articles/573144/Versioning-SQL-Server-Databases-using-SSDT).
* **Block publish when database has drifted from registered version** (`block_when_drift_detected`) - default is `false`.

Deployment behaviour:

* **Deploy database properties** (`script_database_options`) - Default is `true`.
* **Always re-create database** (`create_new_database`) - `true` if database should be re-created every time you deploy. Default is `false`.
* **Block incremental deployment if data loss might occur** (`block_on_possible_data_loss`) - Prevent database changes if data loss might occur. Default is `true`.
* **Execute deployment script in single-user mode** (`deploy_database_in_single_user_mode`) - `true` to execute deployment script in single-user mode. Default is `false`.
* **Back up database before deployment** (`backup_database_before_changes`) - `true` to back up database before deployment. **Does not work when publishing to Azure SQL databse**. Default is `false`.
* **DROP objects in target but not in project** (`drop_objects_not_in_source`) - `true` to remove objects in destination database that do not exist in the package. Default is `false`.
* **Do not user ALTER ASSEMBLY statements to update CLR types** (`no_alter_statements_to_change_clr_types`) - Default is `false`.

Advanced deployment options:

* **Allow drop blocking assemblies** (`allow_drop_blocking_assemblies`) - This property is used by SqlClr deployment to cause any blocking assemblies to be dropped as part of the deployment plan. By default, any blocking/referencing assemblies will block an assembly update if the referencing assembly needs to be dropped. Default is `false`.
* **Allow incompatible platform** (`allow_incompatible_platform`) - Specifies whether to attempt the action despite incompatible SQL Server platforms. Default is `false`.
* **Comment out SetVar declarations** (`comment_out_set_var_declarations`) - Specifies whether the declaration of SETVAR variables should be commented out in the generated publish script. You might choose to do this if you plan to specify the values on the command line when you publish by using a tool such as SQLCMD.EXE. Default is `false`.
* **Compare using target collation** (`compare_using_target_collation`) - This setting dictates how the database's collation is handled during deployment; by default the target database's collation will be updated if it does not match the collation specified by the source. When this option is set, the target database's (or server’s) collation should be used. Default is `false`.
* **Disable and re-enable DDL triggers** (`disable_and_reenable_ddl_triggers`) - Specifies whether Data Definition Language (DDL) triggers are disabled at the beginning of the publish process and re-enabled at the end of the publish action. Default is `true`.
* **Do not alter Change Data Capture objects** (`do_not_alter_change_data_capture_objects`) - If enabled: true, Change Data Capture objects are not altered. Default is `true`.
* **Do not ALTER replicated objects** (`do_not_alter_replicated_objects`) - Specifies whether objects that are replicated are identified during verification. Default is `true`.
* **Drop constraints not in source** (`drop_constraints_not_in_source`) - Specifies whether constraints that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop DML triggers not in source** (`drop_dml_triggers_not_in_source`) - Specifies whether DML triggers that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop extended properties not in source** (`drop_extended_properties_not_in_source`) - Specifies whether extended properties that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop indexes not in source** (`drop_indexes_not_in_source`) - Specifies whether indexes that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop permissions not in source** (`drop_permissions_not_in_source`) - Specifies whether permissions that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database. Default is `false`.
* **Drop role members not defined in source** (`drop_role_members_not_in_source`) - Specifies whether role members that are not defined in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database. Default is `false`.
* **Generate smart defaults, when applicable** (`generate_smart_defaults`) - Automatically provides a default value when updating a table that contains data with a column that does not allow null values. Default is `false`.
* **Ignore ANSI Nulls** (`ignore_ansi_nulls`) - Specifies whether differences in the ANSI NULLS setting should be ignored or updated when you publish to a database. Default is `true`.
* **Ignore authorizer** (`ignore_authorizer`) - Specifies whether differences in the Authorizer should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore column collation** (`ignore_column_collation`) - Specifies whether differences in the column collations should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore comments** (`ignore_comments`) - Specifies whether differences in the comments should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore cryptographic provider file path** (`ignore_cryptographic_provider_file_path`) - Specifies whether differences in the file path for the cryptographic provider should be ignored or updated when you publish to a database. Default is `true`.
* **Ignore DDL trigger order** (`ignore_ddl_trigger_order`) - Specifies whether differences in the order of Data Definition Language (DDL) triggers should be ignored or updated when you publish to a database or server. Default is `false`.
* **Ignore DDL trigger state** (`ignore_ddl_trigger_state`) - Specifies whether differences in the enabled or disabled state of Data Definition Language (DDL) triggers should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore default schema** (`ignore_default_schema`) - Specifies whether differences in the default schema should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore DML trigger order** (`ignore_dml_trigger_order`) - Specifies whether differences in the order of Data Manipulation Language (DML) triggers should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore DML trigger state** (`ignore_dml_trigger_state`) - Specifies whether differences in the enabled or disabled state of DML triggers should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore extended properties** (`ignore_extended_properties`) - Specifies whether differences in the extended properties should be ignored or updated when you publish to a database. Default is `false`.<commit_msg>Do not drop users doc (#254)<commit_after>---
layout: docs
title: Publishing SQL Server databases from SSDT packages
---

<!-- markdownlint-disable MD022 MD032 -->
# Publishing SQL Server databases from SSDT packages
{:.no_toc}

"SQL Database" (`SqlDatabase`) deployment provider allows incremental publishing of database changes from [SQL Server Data Tools](https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx) (SSDT) package (`.dacpac`) to a local SQL Server instance, remote SQL Server or Azure SQL database.

In this guide:

* Comment to trigger ToC generation
{:toc}
<!-- markdownlint-enable MD022 MD032 -->


## Provider settings

* **Artifact** (`artifact`) - `.dacpac` artifact file name or deployment name or regexp matching one of these.
* **Target SQL Server connection string** (`connection_string`) - SQL connection string to the target database. **Must include database name**. For example `server=(local)\SQLEXPRESS;database=test101;Integrated security=SSPI;`
* **Register as a Data-tier Application** (`register_data_tier_application`) - default is `false`. You can read about Data-tier applications on [MSDN](https://msdn.microsoft.com/en-us/library/ee210546.aspx) and also there is a nice introductory article on [CodeProject](http://www.codeproject.com/Articles/573144/Versioning-SQL-Server-Databases-using-SSDT).
* **Block publish when database has drifted from registered version** (`block_when_drift_detected`) - default is `false`.

Deployment behaviour:

* **Deploy database properties** (`script_database_options`) - Default is `true`.
* **Always re-create database** (`create_new_database`) - `true` if database should be re-created every time you deploy. Default is `false`.
* **Block incremental deployment if data loss might occur** (`block_on_possible_data_loss`) - Prevent database changes if data loss might occur. Default is `true`.
* **Execute deployment script in single-user mode** (`deploy_database_in_single_user_mode`) - `true` to execute deployment script in single-user mode. Default is `false`.
* **Back up database before deployment** (`backup_database_before_changes`) - `true` to back up database before deployment. **Does not work when publishing to Azure SQL databse**. Default is `false`.
* **DROP objects in target but not in project** (`drop_objects_not_in_source`) - `true` to remove objects in destination database that do not exist in the package. Default is `false`.
* **Do not DROP users** (`do_not_drop_users`). Set to `true` when **DROP objects in target but not in project** (`drop_objects_not_in_source`) is `true` to preserve database users. Default is `false`. **Important note**: to properly preserve users, the following settings also should be set to `true`:
    * **Ignore permissions** (`ignore_permissions`)
    * **Ignore user settings objects** (`ignore_user_settings_objects`)
    * **Ignore login SIDs** (`ignore_login_sids`)
    * **Ignore role membership** (`ignore_role_membership`)
* **Do not user ALTER ASSEMBLY statements to update CLR types** (`no_alter_statements_to_change_clr_types`) - Default is `false`.

Advanced deployment options:

* **Allow drop blocking assemblies** (`allow_drop_blocking_assemblies`) - This property is used by SqlClr deployment to cause any blocking assemblies to be dropped as part of the deployment plan. By default, any blocking/referencing assemblies will block an assembly update if the referencing assembly needs to be dropped. Default is `false`.
* **Allow incompatible platform** (`allow_incompatible_platform`) - Specifies whether to attempt the action despite incompatible SQL Server platforms. Default is `false`.
* **Comment out SetVar declarations** (`comment_out_set_var_declarations`) - Specifies whether the declaration of SETVAR variables should be commented out in the generated publish script. You might choose to do this if you plan to specify the values on the command line when you publish by using a tool such as SQLCMD.EXE. Default is `false`.
* **Compare using target collation** (`compare_using_target_collation`) - This setting dictates how the database's collation is handled during deployment; by default the target database's collation will be updated if it does not match the collation specified by the source. When this option is set, the target database's (or server’s) collation should be used. Default is `false`.
* **Disable and re-enable DDL triggers** (`disable_and_reenable_ddl_triggers`) - Specifies whether Data Definition Language (DDL) triggers are disabled at the beginning of the publish process and re-enabled at the end of the publish action. Default is `true`.
* **Do not alter Change Data Capture objects** (`do_not_alter_change_data_capture_objects`) - If enabled: true, Change Data Capture objects are not altered. Default is `true`.
* **Do not ALTER replicated objects** (`do_not_alter_replicated_objects`) - Specifies whether objects that are replicated are identified during verification. Default is `true`.
* **Drop constraints not in source** (`drop_constraints_not_in_source`) - Specifies whether constraints that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop DML triggers not in source** (`drop_dml_triggers_not_in_source`) - Specifies whether DML triggers that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop extended properties not in source** (`drop_extended_properties_not_in_source`) - Specifies whether extended properties that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop indexes not in source** (`drop_indexes_not_in_source`) - Specifies whether indexes that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish to a database. Default is `true`.
* **Drop permissions not in source** (`drop_permissions_not_in_source`) - Specifies whether permissions that do not exist in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database. Default is `false`.
* **Drop role members not defined in source** (`drop_role_members_not_in_source`) - Specifies whether role members that are not defined in the database snapshot (.dacpac) file will be dropped from the target database when you publish updates to a database. Default is `false`.
* **Generate smart defaults, when applicable** (`generate_smart_defaults`) - Automatically provides a default value when updating a table that contains data with a column that does not allow null values. Default is `false`.
* **Ignore ANSI Nulls** (`ignore_ansi_nulls`) - Specifies whether differences in the ANSI NULLS setting should be ignored or updated when you publish to a database. Default is `true`.
* **Ignore authorizer** (`ignore_authorizer`) - Specifies whether differences in the Authorizer should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore column collation** (`ignore_column_collation`) - Specifies whether differences in the column collations should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore comments** (`ignore_comments`) - Specifies whether differences in the comments should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore cryptographic provider file path** (`ignore_cryptographic_provider_file_path`) - Specifies whether differences in the file path for the cryptographic provider should be ignored or updated when you publish to a database. Default is `true`.
* **Ignore DDL trigger order** (`ignore_ddl_trigger_order`) - Specifies whether differences in the order of Data Definition Language (DDL) triggers should be ignored or updated when you publish to a database or server. Default is `false`.
* **Ignore DDL trigger state** (`ignore_ddl_trigger_state`) - Specifies whether differences in the enabled or disabled state of Data Definition Language (DDL) triggers should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore default schema** (`ignore_default_schema`) - Specifies whether differences in the default schema should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore DML trigger order** (`ignore_dml_trigger_order`) - Specifies whether differences in the order of Data Manipulation Language (DML) triggers should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore DML trigger state** (`ignore_dml_trigger_state`) - Specifies whether differences in the enabled or disabled state of DML triggers should be ignored or updated when you publish to a database. Default is `false`.
* **Ignore extended properties** (`ignore_extended_properties`) - Specifies whether differences in the extended properties should be ignored or updated when you publish to a database. Default is `false`.
{'fraction_non_alphanumeric': 0.08170212765957446, 'fraction_numerical': 0.00425531914893617, 'mean_word_length': 5.363176895306859, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 7, 'https://': 4, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3093125', 'n_tokens_mistral': 4494, 'n_tokens_neox': 4218, 'n_words': 2212}