Share via


az sql db

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 GA
az sql db geo-backup list

Gets a list of recoverable databases.

Core GA
az sql db geo-backup restore

Restore a geo-redundant backup to a new database.

Core GA
az sql db geo-backup show

Gets a recoverable database, which is a resource representing a database's geo backup.

Core GA
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

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

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

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

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

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.

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

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

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.

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

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

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.

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

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

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.

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

Gets a list of restorable dropped databases.

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

Optional Parameters

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

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

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

Gets database usages.

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

Optional Parameters

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

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

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

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.

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

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

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.

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

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

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

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

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.

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

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

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

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

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.

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

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.

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

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