az sql db

Manage databases.

Commands

az sql db audit-policy Manage a database's auditing policy.
az sql db audit-policy show Gets a database's blob auditing policy.
az sql db audit-policy update Update a database's auditing policy.
az sql db copy Create a copy of a database.
az sql db create Create a database.
az sql db delete Delete a database.
az sql db export Export a database to a bacpac.
az sql db import Imports a bacpac into an existing database.
az sql db list List databases a server or elastic pool.
az sql db list-deleted Gets a list of deleted databases that can be restored.
az sql db list-editions Show database editions available for the currently active subscription.
az sql db list-usages Returns database usages.
az sql db op Manage operations on a database.
az sql db op cancel Cancels the asynchronous operation on the database.
az sql db op list Gets a list of operations performed on the database.
az sql db rename Rename a database.
az sql db replica Manage replication between databases.
az sql db replica create Create a database as a readable secondary replica of an existing database.
az sql db replica delete-link Permanently stop data replication between two database replicas.
az sql db replica list-links List the replicas of a database and their replication status.
az sql db replica set-primary Set the primary replica database by failing over from the current primary replica database.
az sql db restore Create a new database by restoring from a backup.
az sql db show Get the details for a database.
az sql db show-connection-string Generates a connection string to a database.
az sql db tde Manage a database's transparent data encryption.
az sql db tde list-activity Returns a database's transparent data encryption operation result.
az sql db tde set Sets a database's transparent data encryption configuration.
az sql db tde show Gets a database's transparent data encryption configuration.
az sql db threat-policy Manage a database's threat detection policies.
az sql db threat-policy show Gets a database's threat detection policy.
az sql db threat-policy update Update a database's threat detection policy.
az sql db update Update a database.

az sql db copy

Edit

Create a copy of a database.

az sql db copy --dest-name
               [--capacity]
               [--dest-resource-group]
               [--dest-server]
               [--elastic-pool]
               [--family]
               [--ids]
               [--license-type {BasePrice, LicenseIncluded}]
               [--name]
               [--no-wait]
               [--resource-group]
               [--server]
               [--service-objective]
               [--subscription]
               [--tags]
               [--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

Required Parameters

--dest-name

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

Optional Parameters

--capacity -c

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

--dest-resource-group

Name of the resouce 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.

--family -f

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

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

--license-type

The license type to apply for this database.

accepted values: BasePrice, LicenseIncluded
--name -n

Name of the Azure SQL Database.

--no-wait

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

--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 service objective for the new database. For example: Basic, S0, P1, GP_Gen4_1, BC_Gen5_2.

--subscription

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

--tags

Space-separated tags in 'key[=value]' format. Use "" to clear existing tags.

--zone-redundant -z

Specifies whether to enable zone redundancy.

accepted values: false, true

az sql db create

Edit

Create a database.

az sql db create --name
                 --resource-group
                 --server
                 [--capacity]
                 [--catalog-collation {DATABASE_DEFAULT, SQL_Latin1_General_CP1_CI_AS}]
                 [--collation]
                 [--edition]
                 [--elastic-pool]
                 [--family]
                 [--license-type {BasePrice, LicenseIncluded}]
                 [--max-size]
                 [--no-wait]
                 [--sample-name {AdventureWorksLT, WideWorldImportersFull, WideWorldImportersStd}]
                 [--service-objective]
                 [--subscription]
                 [--tags]
                 [--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

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

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

--edition --tier -e

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

--elastic-pool

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

--family -f

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

--license-type

The license type to apply for this database.

accepted values: BasePrice, LicenseIncluded
--max-size

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

--no-wait

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

--sample-name

The name of the sample schema to apply when creating this database.

accepted values: AdventureWorksLT, WideWorldImportersFull, WideWorldImportersStd
--service-objective

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

--subscription

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

--tags

Space-separated tags in 'key[=value]' format. Use "" to clear existing tags.

--zone-redundant -z

Specifies whether to enable zone redundancy.

accepted values: false, true

az sql db delete

Edit

Delete a database.

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

Optional Parameters

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

--name -n

Name of the Azure SQL Database.

--no-wait

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

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

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]
                 [--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 w --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. The password of the SQL administrator.

--admin-user -u

Required. The name of the SQL administrator.

--storage-key

Required. The storage key to use. If storage key type is SharedAccessKey, it must be preceded with a "?.".

--storage-key-type

Required. The type of the storage key to use.

accepted values: SharedAccessKey, StorageAccessKey
--storage-uri

Required. The storage uri to use.

Optional Parameters

--auth-type -a

The authentication type.

accepted values: ADPassword, SQL
--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db import

Edit

Imports a bacpac into an existing database.

az sql db import --admin-password
                 --admin-user
                 --storage-key
                 --storage-key-type {SharedAccessKey, StorageAccessKey}
                 --storage-uri
                 [--auth-type {ADPassword, SQL}]
                 [--ids]
                 [--name]
                 [--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 r --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. The password of the SQL administrator.

--admin-user -u

Required. The name of the SQL administrator.

--storage-key

Required. The storage key to use. If storage key type is SharedAccessKey, it must be preceded with a "?.".

--storage-key-type

Required. The type of the storage key to use.

accepted values: SharedAccessKey, StorageAccessKey
--storage-uri

Required. The storage uri to use.

Optional Parameters

--auth-type -a

The authentication type.

accepted values: ADPassword, SQL
--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db list

Edit

List databases a server or elastic pool.

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

Optional Parameters

--elastic-pool

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

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db list-deleted

Edit

Gets a list of deleted databases that can be restored.

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

Optional Parameters

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db list-editions

Edit

Show database editions available for the currently active subscription.

az sql db list-editions --location
                        [--available]
                        [--dtu]
                        [--edition]
                        [--service-objective]
                        [--show-details {max-size}]
                        [--subscription]
                        [--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.

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

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

--vcores

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

az sql db list-usages

Edit

Returns database usages.

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

Optional Parameters

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db rename

Edit

Rename a database.

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

Required Parameters

--new-name

The new name that the database will be renamed to.

Optional Parameters

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db restore

Edit

Create a new database by restoring from a backup.

az sql db restore --dest-name
                  [--capacity]
                  [--deleted-time]
                  [--edition]
                  [--elastic-pool]
                  [--family]
                  [--ids]
                  [--license-type {BasePrice, LicenseIncluded}]
                  [--name]
                  [--no-wait]
                  [--resource-group]
                  [--server]
                  [--service-objective]
                  [--subscription]
                  [--tags]
                  [--time]
                  [--zone-redundant {false, true}]

Required Parameters

--dest-name

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

Optional Parameters

--capacity -c

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

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

--edition --tier -e

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

--elastic-pool

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

--family -f

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

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

--license-type

The license type to apply for this database.

accepted values: BasePrice, LicenseIncluded
--name -n

Name of the Azure SQL Database.

--no-wait

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

--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 service objective for the new database. For example: Basic, S0, P1, GP_Gen4_1, BC_Gen5_2.

--subscription

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

--tags

Space-separated tags in 'key[=value]' format. 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.

--zone-redundant -z

Specifies whether to enable zone redundancy.

accepted values: false, true

az sql db show

Edit

Get the details for a database.

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

Optional Parameters

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

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

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

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

az sql db update

Edit

Update a database.

az sql db update [--add]
                 [--capacity]
                 [--edition]
                 [--elastic-pool]
                 [--family]
                 [--force-string]
                 [--ids]
                 [--max-size]
                 [--name]
                 [--no-wait]
                 [--remove]
                 [--resource-group]
                 [--server]
                 [--service-objective]
                 [--set]
                 [--subscription]
                 [--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

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

--capacity -c

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

--edition --tier -e

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

--elastic-pool

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

--family -f

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

--force-string

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

--ids

One or more resource IDs (space-delimited). If provided, no other 'Resource Id' arguments should be specified.

--max-size

The new maximum size of the database expressed in bytes.

--name -n

Name of the Azure SQL Database.

--no-wait

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

--remove

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

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

--subscription

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

--zone-redundant -z

Specifies whether to enable zone redundancy.

accepted values: false, true