az sql db

Reference

Manage databases.

Commands

Name	Description	Type	Status
az sql db advanced-threat-protection-setting	Manage a database's advanced threat protection setting.	Core	GA
az sql db advanced-threat-protection-setting show	Gets an advanced threat protection setting.	Core	GA
az sql db advanced-threat-protection-setting update	Update a database's advanced threat protection setting.	Core	GA
az sql db audit-policy	Manage a database's auditing policy.	Core	GA
az sql db audit-policy show	Show database audit policy.	Core	GA
az sql db audit-policy update	Update a database's auditing policy.	Core	GA
az sql db audit-policy wait	Place the CLI in a waiting state until a condition of the database's audit policy is met.	Core	GA
az sql db classification	Manage sensitivity classifications.	Core	GA
az sql db classification delete	Delete the sensitivity classification of a given column.	Core	GA
az sql db classification list	Get the sensitivity classifications of a given database.	Core	GA
az sql db classification recommendation	Manage sensitivity classification recommendations.	Core	GA
az sql db classification recommendation disable	Disable sensitivity recommendations for a given column (recommendations are enabled by default on all columns).	Core	GA
az sql db classification recommendation enable	Enable sensitivity recommendations for a given column (recommendations are enabled by default on all columns).	Core	GA
az sql db classification recommendation list	List the recommended sensitivity classifications of a given database.	Core	GA
az sql db classification show	Get the sensitivity classification of a given column.	Core	GA
az sql db classification update	Update a columns's sensitivity classification.	Core	GA
az sql db copy	Create a copy of a database.	Core	GA
az sql db create	Create a database.	Core	GA
az sql db delete	Delete a database.	Core	GA
az sql db export	Export a database to a bacpac.	Core	GA
az sql db geo-backup	Manage SQL database geo redundant backups.	Core	Preview
az sql db geo-backup list	Gets a list of recoverable databases.	Core	Preview
az sql db geo-backup restore	Restore a geo-redundant backup to a new database.	Core	Preview
az sql db geo-backup show	Gets a recoverable database, which is a resource representing a database's geo backup.	Core	Preview
az sql db import	Imports a bacpac into a new database, or an existing empty database.	Core	GA
az sql db ledger-digest-uploads	Manage ledger digest upload settings.	Core	GA
az sql db ledger-digest-uploads disable	Disable uploading ledger digests.	Core	GA
az sql db ledger-digest-uploads enable	Enable uploading ledger digests to an Azure Storage account or to Azure Confidential Ledger. If uploading ledger digests is already enabled, the cmdlet resets the digest storage endpoint to a new value.	Core	GA
az sql db ledger-digest-uploads show	Show the current ledger digest settings.	Core	GA
az sql db list	List databases on a server or elastic pool.	Core	GA
az sql db list-deleted	Gets a list of restorable dropped databases.	Core	GA
az sql db list-editions	Show database editions available for the currently active subscription.	Core	GA
az sql db list-usages	Gets database usages.	Core	GA
az sql db ltr-backup	Manage SQL database long term retention backups.	Core	Preview
az sql db ltr-backup delete	Delete a long term retention backup.	Core	Preview
az sql db ltr-backup list	List the long term retention backups for a location, server or database.	Core	Preview
az sql db ltr-backup restore	Restore a long term retention backup to a new database.	Core	Preview
az sql db ltr-backup show	Get a long term retention backup for a database.	Core	Preview
az sql db ltr-backup wait	Place the CLI in a waiting state until a condition of the database is met.	Core	Preview
az sql db ltr-policy	Manage SQL database long term retention policy.	Core	Preview
az sql db ltr-policy set	Update long term retention settings for a database.	Core	Preview
az sql db ltr-policy show	Show the long term retention policy for a database.	Core	Preview
az sql db op	Manage operations on a database.	Core	GA
az sql db op cancel	Cancels the asynchronous operation on the database.	Core	GA
az sql db op list	Gets a list of operations performed on the database.	Core	GA
az sql db rename	Rename a database.	Core	GA
az sql db replica	Manage replication between databases.	Core	GA
az sql db replica create	Create a database as a readable secondary replica of an existing database.	Core	GA
az sql db replica delete-link	Permanently stop data replication between two database replicas.	Core	GA
az sql db replica list-links	List the replicas of a database and their replication status.	Core	GA
az sql db replica set-primary	Set the primary replica database by failing over from the current primary replica database.	Core	GA
az sql db restore	Create a new database by restoring from a backup.	Core	GA
az sql db show	Get the details for a database.	Core	GA
az sql db show-connection-string	Generates a connection string to a database.	Core	GA
az sql db show-deleted	Get the details for a deleted database.	Core	GA
az sql db str-policy	Manage SQL database short term retention policy.	Core	Preview
az sql db str-policy set	Update short term retention settings for a live database.	Core	Preview
az sql db str-policy show	Show the short term retention policy for a live database.	Core	Preview
az sql db str-policy wait	Place the CLI in a waiting state until the policy is set.	Core	Preview
az sql db tde	Manage a database's transparent data encryption.	Core	GA
az sql db tde key	Manage a database's encryption protector.	Core	GA
az sql db tde key revalidate	Revalidates a database's encryption protector key.	Core	GA
az sql db tde key revert	Reverts a database's encryption protector key to server level.	Core	GA
az sql db tde set	Sets a database's transparent data encryption configuration.	Core	GA
az sql db tde show	Shows a Transparent Data Encryption.	Core	GA
az sql db threat-policy	Manage a database's threat detection policies.	Core	Deprecated
az sql db threat-policy show	Gets a threat detection policy.	Core	Deprecated
az sql db threat-policy update	Update a database's threat detection policy.	Core	Deprecated
az sql db update	Update a database.	Core	GA

az sql db copy

Edit

Create a copy of a database.

A full list of performance level options can be seen by executing az sql db list-editions -a -o table -l LOCATION. The copy destination database must have the same edition as the source database, but you can change the edition after the copy has completed.

az sql db copy --dest-name
               [--assign-identity {false, true}]
               [--auto-pause-delay]
               [--availability-zone]
               [--backup-storage-redundancy]
               [--capacity]
               [--compute-model {Provisioned, Serverless}]
               [--dest-resource-group]
               [--dest-server]
               [--elastic-pool]
               [--encryption-protector]
               [--encryption-protector-auto-rotation {false, true}]
               [--family]
               [--federated-client-id]
               [--ha-replicas]
               [--ids]
               [--keys]
               [--license-type {BasePrice, LicenseIncluded}]
               [--min-capacity]
               [--name]
               [--no-wait]
               [--preferred-enclave-type {Default, VBS}]
               [--read-scale {Disabled, Enabled}]
               [--resource-group]
               [--server]
               [--service-level-objective]
               [--subscription]
               [--tags]
               [--umi]
               [--zone-redundant {false, true}]

Examples

Create a database with performance level S0 as a copy of an existing Standard database.

az sql db copy -g mygroup -s myserver -n originalDb --dest-name newDb --service-objective S0

Create a database with GeneralPurpose edition, Gen4 hardware, and 1 vcore as a copy of an existing GeneralPurpose database.

az sql db copy -g mygroup -s myserver -n originalDb --dest-name newDb -f Gen4 -c 1

Create a database with local backup storage redundancy as a copy of an existing database

az sql db copy -g mygroup -s myserver -n originalDb --dest-name newDb --backup-storage-redundancy Local

Required Parameters

--dest-name

Name of the database that will be created as the copy destination.

Optional Parameters

--assign-identity -i

Assign identity for database.

Accepted values: false, true

--auto-pause-delay

Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled.

--availability-zone

Availability zone.

--backup-storage-redundancy --bsr

Backup storage redundancy used to store backups. Allowed values include: Local, Zone, Geo, GeoZone.

--capacity -c

The capacity component of the sku in integer number of DTUs or vcores.

--compute-model

The compute model of the database.

Accepted values: Provisioned, Serverless

--dest-resource-group

Name of the resource group to create the copy in. If unspecified, defaults to the origin resource group.

--dest-server

Name of the server to create the copy in. If unspecified, defaults to the origin server.

--elastic-pool

The name or resource id of the elastic pool to create the database in.

--encryption-protector

Specifies the Azure key vault key to be used as database encryption protector key.

--encryption-protector-auto-rotation --epauto

Specifies the database encryption protector key auto rotation flag. Can be either true, false or null.

Accepted values: false, true

--family -f

The compute generation component of the sku (for vcore skus only). Allowed values include: Gen4, Gen5.

--federated-client-id

The federated client id for the SQL Database. It is used for cross tenant CMK scenario.

--ha-replicas --read-replicas

The number of high availability replicas to provision for the database. Only settable for Hyperscale edition.

--ids

One or more resource IDs (space-delimited). It should be a complete resource ID containing all information of 'Resource Id' arguments. You should provide either --ids or other 'Resource Id' arguments.

--keys

The list of AKV keys for the SQL Database.

--license-type

The license type to apply for this database.LicenseIncluded if you need a license, or BasePriceif you have a license and are eligible for the Azure HybridBenefit.

Accepted values: BasePrice, LicenseIncluded

--min-capacity

Minimal capacity that database will always have allocated, if not paused.

--name -n

Name of the Azure SQL Database.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--preferred-enclave-type

Specifies type of enclave for this resource.

Accepted values: Default, VBS

--read-scale

If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica. This property is only settable for Premium and Business Critical databases.

Accepted values: Disabled, Enabled

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--service-level-objective --service-objective

The service objective for the new database. For example: Basic, S0, P1, GP_Gen4_1, GP_S_Gen5_8, BC_Gen5_2, HS_Gen5_32.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--tags

Space-separated tags: key[=value] [key[=value] ...]. Use "" to clear existing tags.

--umi --user-assigned-identity-id

The list of user assigned identity for the SQL Database.

--zone-redundant -z

Specifies whether to enable zone redundancy. Default is true if no value is specified.

Accepted values: false, true

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db create

Edit

Create a database.

A full list of performance level options can be seen by executing az sql db list-editions -a -o table -l LOCATION.

az sql db create --name
                 --resource-group
                 --server
                 [--assign-identity {false, true}]
                 [--auto-pause-delay]
                 [--availability-zone]
                 [--backup-storage-redundancy]
                 [--capacity]
                 [--catalog-collation {DATABASE_DEFAULT, SQL_Latin1_General_CP1_CI_AS}]
                 [--collation]
                 [--compute-model {Provisioned, Serverless}]
                 [--edition]
                 [--elastic-pool]
                 [--encryption-protector]
                 [--encryption-protector-auto-rotation {false, true}]
                 [--exhaustion-behavior {AutoPause, BillOverUsage}]
                 [--family]
                 [--federated-client-id]
                 [--free-limit {false, true}]
                 [--ha-replicas]
                 [--keys]
                 [--ledger-on {Disabled, Enabled}]
                 [--license-type {BasePrice, LicenseIncluded}]
                 [--maint-config-id]
                 [--max-size]
                 [--min-capacity]
                 [--no-wait]
                 [--preferred-enclave-type {Default, VBS}]
                 [--read-scale {Disabled, Enabled}]
                 [--sample-name {AdventureWorksLT}]
                 [--service-level-objective]
                 [--tags]
                 [--umi]
                 [--yes]
                 [--zone-redundant {false, true}]

Examples

Create a Standard S0 database.

az sql db create -g mygroup -s myserver -n mydb --service-objective S0

Create a database with GeneralPurpose edition, Gen4 hardware and 1 vcore

az sql db create -g mygroup -s myserver -n mydb -e GeneralPurpose -f Gen4 -c 1

Create a database with zone redundancy enabled

az sql db create -g mygroup -s myserver -n mydb -z

Create a database with zone redundancy explicitly disabled

az sql db create -g mygroup -s myserver -n mydb -z false

Create a GeneralPurpose Gen5 2 vcore serverless database with auto pause delay of 120 minutes

az sql db create -g mygroup -s myserver -n mydb -e GeneralPurpose -f Gen5 -c 2 --compute-model Serverless --auto-pause-delay 120

Create a Hyperscale Gen5 2 vcore database with 2 read replicas

az sql db create -g mygroup -s myserver -n mydb -e Hyperscale -f Gen5 -c 2 --read-replicas 2

Create a GeneralPurpose database with locally redundant backup storage

az sql db create -g mygroup -s myserver -n mydb -e GeneralPurpose --backup-storage-redundancy Local

Create a database with VBS enclave enabled.

az sql db create -g mygroup -s myserver -n mydb --preferred-enclave-type VBS

Create a database with free limit applied

az sql db create -g mygroup -s myserver -n mydb -e GeneralPurpose -f Gen5 -c 2 --compute-model Serverless --use-free-limit --free-limit-exhaustion-behavior AutoPause

Required Parameters

--name -n

Name of the Azure SQL Database.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

Optional Parameters

--assign-identity -i

Assign identity for database.

Accepted values: false, true

--auto-pause-delay

Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled.

--availability-zone

Availability zone.

--backup-storage-redundancy --bsr

Backup storage redundancy used to store backups. Allowed values include: Local, Zone, Geo, GeoZone.

--capacity -c

The capacity component of the sku in integer number of DTUs or vcores.

--catalog-collation

Collation of the metadata catalog.

Accepted values: DATABASE_DEFAULT, SQL_Latin1_General_CP1_CI_AS

--collation

The collation of the database.

--compute-model

The compute model of the database.

Accepted values: Provisioned, Serverless

--edition --tier -e

The edition component of the sku. Allowed values include: Basic, Standard, Premium, GeneralPurpose, BusinessCritical, Hyperscale.

--elastic-pool

The name or resource id of the elastic pool to create the database in.

--encryption-protector

Specifies the Azure key vault key to be used as database encryption protector key.

--encryption-protector-auto-rotation --epauto

Specifies the database encryption protector key auto rotation flag. Can be either true, false or null.

Accepted values: false, true

--exhaustion-behavior --fleb --free-limit-exhaustion-behavior

Specifies the behavior when monthly free limits are exhausted for the free database.AutoPause: The database will be auto paused upon exhaustion of free limits for remainder of the month.BillForUsage: The database will continue to be online upon exhaustion of free limitsand any overage will be billed.

Accepted values: AutoPause, BillOverUsage

--family -f

The compute generation component of the sku (for vcore skus only). Allowed values include: Gen4, Gen5.

--federated-client-id

The federated client id for the SQL Database. It is used for cross tenant CMK scenario.

--free-limit --use-free-limit

Whether or not the database uses free monthly limits. Allowed on one database in a subscription.

Accepted values: false, true

--ha-replicas --read-replicas

The number of high availability replicas to provision for the database. Only settable for Hyperscale edition.

--keys

The list of AKV keys for the SQL Database.

--ledger-on

Create a ledger database, in which the integrity of all data is protected by the ledger feature. All tables in the ledger database must be ledger tables. Note: the value of this property cannot be changed after the database has been created.

Accepted values: Disabled, Enabled

--license-type

The license type to apply for this database.LicenseIncluded if you need a license, or BasePriceif you have a license and are eligible for the Azure HybridBenefit.

Accepted values: BasePrice, LicenseIncluded

--maint-config-id -m

Specified maintenance configuration id or name for this resource.

--max-size

The max storage size. If no unit is specified, defaults to bytes (B).

--min-capacity

Minimal capacity that database will always have allocated, if not paused.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--preferred-enclave-type

Specifies type of enclave for this resource.

Accepted values: Default, VBS

--read-scale

Accepted values: Disabled, Enabled

--sample-name

The name of the sample schema to apply when creating thisdatabase.

Accepted values: AdventureWorksLT

--service-level-objective --service-objective

The service objective for the new database. For example: Basic, S0, P1, GP_Gen4_1, GP_S_Gen5_8, BC_Gen5_2, HS_Gen5_32.

--tags

Space-separated tags: key[=value] [key[=value] ...]. Use "" to clear existing tags.

--umi --user-assigned-identity-id

The list of user assigned identity for the SQL Database.

--yes -y

Do not prompt for confirmation.

--zone-redundant -z

Specifies whether to enable zone redundancy. Default is true if no value is specified.

Accepted values: false, true

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db delete

Edit

Delete a database.

az sql db delete [--ids]
                 [--name]
                 [--no-wait]
                 [--resource-group]
                 [--server]
                 [--subscription]
                 [--yes]

Examples

Delete a database. (autogenerated)

az sql db delete --name MyAzureSQLDatabase --resource-group MyResourceGroup --server myserver

Optional Parameters

--ids

--name -n

Name of the Azure SQL Database.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--yes -y

Do not prompt for confirmation.

Default value: False

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db export

Edit

Export a database to a bacpac.

az sql db export --admin-password
                 --admin-user
                 --storage-key
                 --storage-key-type {SharedAccessKey, StorageAccessKey}
                 --storage-uri
                 [--auth-type {ADPassword, SQL}]
                 [--ids]
                 [--name]
                 [--no-wait]
                 [--resource-group]
                 [--server]
                 [--subscription]

Examples

Get an SAS key for use in export operation.

az storage blob generate-sas --account-name myAccountName -c myContainer -n myBacpac.bacpac \
    --permissions rw --expiry 2018-01-01T00:00:00Z

Export bacpac using an SAS key.

az sql db export -s myserver -n mydatabase -g mygroup -p password -u login \
    --storage-key "?sr=b&sp=rw&se=2018-01-01T00%3A00%3A00Z&sig=mysignature&sv=2015-07-08" \
    --storage-key-type SharedAccessKey \
    --storage-uri https://myAccountName.blob.core.windows.net/myContainer/myBacpac.bacpac

Export bacpac using a storage account key.

az sql db export -s myserver -n mydatabase -g mygroup -p password -u login \
    --storage-key MYKEY== --storage-key-type StorageAccessKey \
    --storage-uri https://myAccountName.blob.core.windows.net/myContainer/myBacpac.bacpac

Required Parameters

--admin-password -p

Required. Administrator login password.

--admin-user -u

Required. Administrator login name.

--storage-key

Required. Storage key.

--storage-key-type

Required. Storage key type.

Accepted values: SharedAccessKey, StorageAccessKey

--storage-uri

Required. Storage Uri.

Optional Parameters

--auth-type -a

Authentication type.

Accepted values: ADPassword, SQL

--ids

--name -n

Name of the Azure SQL Database.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db import

Edit

Imports a bacpac into a new database, or an existing empty database.

az sql db import --admin-password
                 --admin-user
                 --storage-key
                 --storage-key-type {SharedAccessKey, StorageAccessKey}
                 --storage-uri
                 [--auth-type {ADPassword, SQL}]
                 [--ids]
                 [--name]
                 [--no-wait]
                 [--resource-group]
                 [--server]
                 [--subscription]

Examples

Get an SAS key for use in import operation.

az storage blob generate-sas --account-name myAccountName -c myContainer -n myBacpac.bacpac \
    --permissions rw --expiry 2018-01-01T00:00:00Z

Import bacpac into an existing database using an SAS key.

az sql db import -s myserver -n mydatabase -g mygroup -p password -u login \
    --storage-key "?sr=b&sp=rw&se=2018-01-01T00%3A00%3A00Z&sig=mysignature&sv=2015-07-08" \
    --storage-key-type SharedAccessKey \
    --storage-uri https://myAccountName.blob.core.windows.net/myContainer/myBacpac.bacpac

Import bacpac into an existing database using a storage account key.

az sql db import -s myserver -n mydatabase -g mygroup -p password -u login --storage-key MYKEY== \
    --storage-key-type StorageAccessKey \
    --storage-uri https://myAccountName.blob.core.windows.net/myContainer/myBacpac.bacpac

Required Parameters

--admin-password -p

Required. Administrator login password.

--admin-user -u

Required. Administrator login name.

--storage-key

Required. Storage key.

--storage-key-type

Required. Storage key type.

Accepted values: SharedAccessKey, StorageAccessKey

--storage-uri

Required. Storage Uri.

Optional Parameters

--auth-type -a

Authentication type.

Accepted values: ADPassword, SQL

--ids

--name -n

Name of the Azure SQL Database.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db list

Edit

List databases on a server or elastic pool.

az sql db list [--elastic-pool]
               [--ids]
               [--resource-group]
               [--server]
               [--subscription]

Examples

List databases on a server or elastic pool. (autogenerated)

az sql db list --resource-group MyResourceGroup --server myserver

Optional Parameters

--elastic-pool

If specified, lists only the databases in this elastic pool.

--ids

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db list-deleted

Edit

Gets a list of restorable dropped databases.

az sql db list-deleted [--ids]
                       [--resource-group]
                       [--server]
                       [--subscription]

Optional Parameters

--ids

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db list-editions

Edit

Show database editions available for the currently active subscription.

Includes available service objectives and storage limits. In order to reduce verbosity, settings to intentionally reduce storage limits are hidden by default.

az sql db list-editions --location
                        [--available]
                        [--dtu]
                        [--edition]
                        [--service-objective]
                        [--show-details {max-size}]
                        [--vcores]

Examples

Show all database editions in a location.

az sql db list-editions -l westus -o table

Show all available database service objectives for Standard edition.

az sql db list-editions -l westus --edition Standard -o table

Show available max database sizes for P1 service objective

az sql db list-editions -l westus --service-objective P1 --show-details max-size

Required Parameters

--location -l

Location. Values from: az account list-locations. You can configure the default location using az configure --defaults location=<location>.

Optional Parameters

--available -a

If specified, show only results that are available in the specified region.

Default value: False

--dtu

Number of DTUs to search for. If unspecified, all DTU sizes are shown.

--edition --tier -e

Edition to search for. If unspecified, all editions are shown.

--service-objective

Service objective to search for. If unspecified, all service objectives are shown.

--show-details -d

List of additional details to include in output.

Accepted values: max-size

--vcores

Number of vcores to search for. If unspecified, all vcore sizes are shown.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db list-usages

Edit

Gets database usages.

az sql db list-usages [--ids]
                      [--name]
                      [--resource-group]
                      [--server]
                      [--subscription]

Optional Parameters

--ids

--name -n

Name of the Azure SQL Database.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db rename

Edit

Rename a database.

az sql db rename --new-name
                 [--ids]
                 [--name]
                 [--resource-group]
                 [--server]
                 [--subscription]

Examples

Rename a database. (autogenerated)

az sql db rename --name MyAzureSQLDatabase --new-name MyNew --resource-group MyResourceGroup --server myserver

Required Parameters

--new-name

The new name that the database will be renamed to.

Optional Parameters

--ids

--name -n

Name of the Azure SQL Database.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db restore

Edit

Create a new database by restoring from a backup.

az sql db restore --dest-name
                  [--assign-identity {false, true}]
                  [--auto-pause-delay]
                  [--availability-zone]
                  [--backup-storage-redundancy]
                  [--capacity]
                  [--compute-model {Provisioned, Serverless}]
                  [--deleted-time]
                  [--edition]
                  [--elastic-pool]
                  [--encryption-protector]
                  [--encryption-protector-auto-rotation {false, true}]
                  [--family]
                  [--federated-client-id]
                  [--ha-replicas]
                  [--ids]
                  [--keys]
                  [--license-type {BasePrice, LicenseIncluded}]
                  [--min-capacity]
                  [--name]
                  [--no-wait]
                  [--preferred-enclave-type {Default, VBS}]
                  [--read-scale {Disabled, Enabled}]
                  [--resource-group]
                  [--server]
                  [--service-level-objective]
                  [--subscription]
                  [--tags]
                  [--time]
                  [--umi]
                  [--zone-redundant {false, true}]

Examples

Create a new database by restoring from a backup. (autogenerated)

az sql db restore --dest-name MyDest --edition GeneralPurpose --name MyAzureSQLDatabase --resource-group MyResourceGroup --server myserver --subscription MySubscription --time "2018-05-20T05:34:22"

Create a new database with geo-redundant backup storage by restoring from a backup. (autogenerated)

az sql db restore --dest-name MyDest --edition GeneralPurpose --name MyAzureSQLDatabase --resource-group MyResourceGroup --server myserver --subscription MySubscription --time "2018-05-20T05:34:22" --backup-storage-redundancy Geo

Required Parameters

--dest-name

Name of the database that will be created as the restore destination.

Optional Parameters

--assign-identity -i

Assign identity for database.

Accepted values: false, true

--auto-pause-delay

Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled.

--availability-zone

Availability zone.

--backup-storage-redundancy --bsr

Backup storage redundancy used to store backups. Allowed values include: Local, Zone, Geo, GeoZone.

--capacity -c

The capacity component of the sku in integer number of DTUs or vcores.

--compute-model

The compute model of the database.

Accepted values: Provisioned, Serverless

--deleted-time

If specified, restore from a deleted database instead of from an existing database. Must match the deleted time of a deleted database in the same server. Either --time or --deleted-time (or both) must be specified. Time should be in following format: "YYYY-MM-DDTHH:MM:SS".

--edition --tier -e

The edition component of the sku. Allowed values include: Basic, Standard, Premium, GeneralPurpose, BusinessCritical, Hyperscale.

--elastic-pool

The name or resource id of the elastic pool to create the database in.

--encryption-protector

Specifies the Azure key vault key to be used as database encryption protector key.

--encryption-protector-auto-rotation --epauto

Specifies the database encryption protector key auto rotation flag. Can be either true, false or null.

Accepted values: false, true

--family -f

The compute generation component of the sku (for vcore skus only). Allowed values include: Gen4, Gen5.

--federated-client-id

The federated client id for the SQL Database. It is used for cross tenant CMK scenario.

--ha-replicas --read-replicas

The number of high availability replicas to provision for the database. Only settable for Hyperscale edition.

--ids

--keys

The list of AKV keys for the SQL Database.

--license-type

The license type to apply for this database.LicenseIncluded if you need a license, or BasePriceif you have a license and are eligible for the Azure HybridBenefit.

Accepted values: BasePrice, LicenseIncluded

--min-capacity

Minimal capacity that database will always have allocated, if not paused.

--name -n

Name of the Azure SQL Database.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--preferred-enclave-type

Specifies type of enclave for this resource.

Accepted values: Default, VBS

--read-scale

Accepted values: Disabled, Enabled

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--service-level-objective --service-objective

The service objective for the new database. For example: Basic, S0, P1, GP_Gen4_1, GP_S_Gen5_8, BC_Gen5_2, HS_Gen5_32.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--tags

Space-separated tags: key[=value] [key[=value] ...]. Use "" to clear existing tags.

--time -t

The point in time of the source database that will be restored to create the new database. Must be greater than or equal to the source database's earliestRestoreDate value. Either --time or --deleted-time (or both) must be specified. Time should be in following format: "YYYY-MM-DDTHH:MM:SS".

--umi --user-assigned-identity-id

The list of user assigned identity for the SQL Database.

--zone-redundant -z

Specifies whether to enable zone redundancy. Default is true if no value is specified.

Accepted values: false, true

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db show

Edit

Get the details for a database.

az sql db show [--expand-keys {false, true}]
               [--ids]
               [--keys-filter]
               [--name]
               [--resource-group]
               [--server]
               [--subscription]

Examples

Get the details for a database. (autogenerated)

az sql db show --name MyAzureSQLDatabase --resource-group MyResourceGroup --server myserver

Optional Parameters

--expand-keys

Expand the AKV keys for the database.

Accepted values: false, true

Default value: False

--ids

--keys-filter

Expand the AKV keys for the database.

--name -n

Name of the Azure SQL Database.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db show-connection-string

Edit

Generates a connection string to a database.

az sql db show-connection-string --client {ado.net, jdbc, odbc, php, php_pdo, sqlcmd}
                                 [--auth-type {ADIntegrated, ADPassword, SqlPassword}]
                                 [--ids]
                                 [--name]
                                 [--server]
                                 [--subscription]

Examples

Generate connection string for ado.net

az sql db show-connection-string -s myserver -n mydb -c ado.net

Required Parameters

--client -c

Type of client connection provider.

Accepted values: ado.net, jdbc, odbc, php, php_pdo, sqlcmd

Optional Parameters

--auth-type -a

Type of authentication.

Accepted values: ADIntegrated, ADPassword, SqlPassword

Default value: SqlPassword

--ids

--name -n

Name of the Azure SQL Database.

Default value: <databasename>

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

Default value: <servername>

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db show-deleted

Edit

Get the details for a deleted database.

az sql db show-deleted --restorable-dropped-database-id
                       [--expand-keys {false, true}]
                       [--ids]
                       [--keys-filter]
                       [--resource-group]
                       [--server]
                       [--subscription]

Examples

Get the details for a deleted database. (autogenerated)

az sql db show-deleted --resource-group MyResourceGroup --server myserver --restorable-dropped-database-id "MyAzureSQLDatabase,133203966918270000"

Required Parameters

--restorable-dropped-database-id -r

Restorable dropped database id.

Optional Parameters

--expand-keys

Expand the AKV keys for the database.

Accepted values: false, true

Default value: False

--ids

--keys-filter

Expand the AKV keys for the database.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

az sql db update

Edit

Update a database.

az sql db update [--add]
                 [--assign-identity {false, true}]
                 [--auto-pause-delay]
                 [--backup-storage-redundancy]
                 [--capacity]
                 [--compute-model {Provisioned, Serverless}]
                 [--edition]
                 [--elastic-pool]
                 [--encryption-protector]
                 [--encryption-protector-auto-rotation {false, true}]
                 [--exhaustion-behavior {AutoPause, BillOverUsage}]
                 [--family]
                 [--federated-client-id]
                 [--filter]
                 [--force-string]
                 [--free-limit {false, true}]
                 [--ha-replicas]
                 [--ids]
                 [--keys]
                 [--keys-to-remove]
                 [--maint-config-id]
                 [--manual-cutover {false, true}]
                 [--max-size]
                 [--min-capacity]
                 [--name]
                 [--no-wait]
                 [--perform-cutover {false, true}]
                 [--preferred-enclave-type {Default, VBS}]
                 [--read-scale {Disabled, Enabled}]
                 [--remove]
                 [--resource-group]
                 [--server]
                 [--service-objective]
                 [--set]
                 [--subscription]
                 [--umi]
                 [--zone-redundant {false, true}]

Examples

Update a database to Standard edition, S0 performance level (10 DTU) by specifying DTU capacity. Note that GeneralPurpose allows a wider range of max size than Standard edition.

az sql db update -g mygroup -s myserver -n mydb --edition Standard --capacity 10 --max-size 250GB

Update a database to Standard edition, S1 performance level (20 DTU) by specifying performance level name. Note that GeneralPurpose allows a wider range of max size than Standard edition.

az sql db update -g mygroup -s myserver -n mydb --edition Standard --service-objective S1 --max-size 250GB

Update a database to GeneralPurpose edition, 4 vcores with Gen5 hardware

az sql db update -g mygroup -s myserver -n mydb --edition GeneralPurpose --capacity 4 --family Gen5

Update database with increased max size

az sql db update -g mygroup -s myserver -n mydb --max-size 500GB

Update database with zone redundancy enabled

az sql db update -g mygroup -s myserver -n mydb -z

Update database with zone redundancy explicitly disabled

az sql db update -g mygroup -s myserver -n mydb -z false

Update database to serverless compute model

az sql db update -g mygroup -s myserver -n mydb --edition GeneralPurpose --capacity 2 --family Gen5 --compute-model Serverless

Update database with locally redundant backup storage

az sql db update -g mygroup -s myserver -n mydb --backup-storage-redundancy Local

Update database with VBS enclave enabled.

az sql db update -g mygroup -s myserver -n mydb --preferred-enclave-type VBS

Update exhaustion behavior of free limit database to BillOverUsage

az sql db update -g mygroup -s myserver -n mydb --free-limit-exhaustion-behavior BillOverUsage

Update a database to Hyperscale edition, 2 vcores with Gen5 hardware, with manual cutover option

az sql db update -g mygroup -s myserver -n mydb --edition Hyperscale --service-objective HS_Gen5_2 --manual-cutover

Trigger cutover with perform cutover option when update database to Hyperscale edition is in progress

az sql db update -g mygroup -s myserver -n mydb --perform-cutover

Optional Parameters

--add

Add an object to a list of objects by specifying a path and key value pairs. Example: --add property.listProperty <key=value, string or JSON string>.

Default value: []

--assign-identity -i

Assign identity for database.

Accepted values: false, true

Default value: False

--auto-pause-delay

Time in minutes after which database is automatically paused. A value of -1 means that automatic pause is disabled.

--backup-storage-redundancy --bsr

Backup storage redundancy used to store backups. Allowed values include: Local, Zone, Geo, GeoZone.

--capacity -c

The capacity component of the sku in integer number of DTUs or vcores.

--compute-model

The compute model of the database.

Accepted values: Provisioned, Serverless

--edition --tier -e

The edition component of the sku. Allowed values include: Basic, Standard, Premium, GeneralPurpose, BusinessCritical, Hyperscale.

--elastic-pool

The name or resource id of the elastic pool to move the database into.

--encryption-protector

Specifies the Azure key vault key to be used as database encryption protector key.

--encryption-protector-auto-rotation --epauto

Specifies the database encryption protector key auto rotation flag. Can be either true, false or null.

Accepted values: false, true

--exhaustion-behavior --fleb --free-limit-exhaustion-behavior

Accepted values: AutoPause, BillOverUsage

--family -f

The compute generation component of the sku (for vcore skus only). Allowed values include: Gen4, Gen5.

--federated-client-id

The federated client id for the SQL Database. It is used for cross tenant CMK scenario.

--filter

An OData filter expression that filters elements in the collection. Default value is None.

--force-string

When using 'set' or 'add', preserve string literals instead of attempting to convert to JSON.

Default value: False

--free-limit --use-free-limit

Whether or not the database uses free monthly limits. Allowed on one database in a subscription.

Accepted values: false, true

--ha-replicas --read-replicas

The number of high availability replicas to provision for the database. Only settable for Hyperscale edition.

--ids

--keys

The list of AKV keys for the SQL Database.

--keys-to-remove

The list of AKV keys to remove from the SQL Database.

--maint-config-id -m

Specified maintenance configuration id or name for this resource.

--manual-cutover

Whether to do manual cutover during Update SLO. Allowed when updating database to Hyperscale tier.

Accepted values: false, true

--max-size

The new maximum size of the database expressed in bytes.

--min-capacity

Minimal capacity that database will always have allocated, if not paused.

--name -n

Name of the Azure SQL Database.

--no-wait

Do not wait for the long-running operation to finish.

Default value: False

--perform-cutover

Whether to perform cutover when updating database to Hyperscale tier is in progress.

Accepted values: false, true

--preferred-enclave-type

Specifies type of enclave for this resource.

Accepted values: Default, VBS

--read-scale

Accepted values: Disabled, Enabled

--remove

Remove a property or an element from a list. Example: --remove property.list <indexToRemove> OR --remove propertyToRemove.

Default value: []

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=<name>.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=<name>.

--service-objective

The name of the new service objective. If this is a standalone db service objective and the db is currently in an elastic pool, then the db is removed from the pool.

--set

Update an object by specifying a property path and value to set. Example: --set property1.property2=<value>.

Default value: []

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--umi --user-assigned-identity-id

The list of user assigned identity for the SQL Database.

--zone-redundant -z

Specifies whether to enable zone redundancy. Default is true if no value is specified.

Accepted values: false, true

Global Parameters

--debug

Increase logging verbosity to show all debug logs.

--help -h

Show this help message and exit.

--only-show-errors

Only show errors, suppressing warnings.

--output -o

Output format.

Accepted values: json, jsonc, none, table, tsv, yaml, yamlc

Default value: json

--query

JMESPath query string. See http://jmespath.org/ for more information and examples.

--subscription

Name or ID of subscription. You can configure the default subscription using az account set -s NAME_OR_ID.

--verbose

Increase logging verbosity. Use --debug for full debug logs.

Share via

az sql db

Commands

az sql db copy

Examples

Required Parameters

Optional Parameters

az sql db create

Examples

Required Parameters

Optional Parameters

az sql db delete

Examples

Optional Parameters

az sql db export

Examples

Required Parameters

Optional Parameters

az sql db import

Examples

Required Parameters

Optional Parameters

az sql db list

Examples

Optional Parameters

az sql db list-deleted

Optional Parameters

az sql db list-editions

Examples

Required Parameters

Optional Parameters

az sql db list-usages

Optional Parameters

az sql db rename

Examples

Required Parameters

Optional Parameters

az sql db restore

Examples

Required Parameters

Optional Parameters

az sql db show

Examples

Optional Parameters

az sql db show-connection-string

Examples

Required Parameters

Optional Parameters

az sql db show-deleted

Examples

Required Parameters

Optional Parameters

az sql db update

Examples

Optional Parameters

Additional resources