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

Create a copy of a database.

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

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

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.

--license-type

The license type to apply for this database.

accepted values: BasePrice, LicenseIncluded
--no-wait

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

--service-objective

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

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

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]
[--service-objective]
[--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.

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.

--service-objective

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

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

Delete a database.

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

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.

Optional Parameters

--no-wait

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

--yes -y

Do not prompt for confirmation.

az sql db export

Export a database to a bacpac.

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

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://mystorageaccount.blob.core.windows.net/bacpacs/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://mystorageaccount.blob.core.windows.net/bacpacs/mybacpac.bacpac

Required Parameters

--admin-password -p

Required. The password of the SQL administrator.

--admin-user -u

Required. The name of the SQL administrator.

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

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

az sql db import

Imports a bacpac into an existing database.

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

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://mystorageaccount.blob.core.windows.net/bacpacs/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://mystorageaccount.blob.core.windows.net/bacpacs/mybacpac.bacpac

Required Parameters

--admin-password -p

Required. The password of the SQL administrator.

--admin-user -u

Required. The name of the SQL administrator.

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

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

az sql db list

List databases a server or elastic pool.

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

Required Parameters

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

Optional Parameters

--elastic-pool

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

az sql db list-deleted

Gets a list of deleted databases that can be restored.

az sql db list-deleted --resource-group
--server

Required Parameters

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

az sql db list-editions

Show database editions available for the currently active subscription.

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

Show all available database service objectives for Standard edition.

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

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

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

az sql db list-usages

Returns database usages.

az sql db list-usages --name
--resource-group
--server

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.

az sql db rename

Rename a database.

az sql db rename --name
--new-name
--resource-group
--server

Required Parameters

--name -n

Name of the Azure SQL Database.

--new-name

The new name that the database will be renamed to.

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

az sql db restore

Create a new database by restoring from a backup.

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

Required Parameters

--dest-name

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

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

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.

--license-type

The license type to apply for this database.

accepted values: BasePrice, LicenseIncluded
--no-wait

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

--service-objective

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

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

Get the details for a database.

az sql db show --name
--resource-group
--server

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.

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}]
[--name]
[--server]

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

Name of the Azure SQL Database.

default value: <databasename>
--server -s

Name of the Azure SQL server.

default value: <servername>

az sql db update

Update a database.

az sql db update --name
--resource-group
--server
[--add]
[--capacity]
[--edition]
[--elastic-pool]
[--family]
[--max-size]
[--no-wait]
[--remove]
[--service-objective]
[--set]
[--zone-redundant {false, true}]

Examples

Update database with zone redundancy enabled

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

Update database with zone redundancy explicitly disabled

az sql db update -g mygroup -s myserver -n mypool -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.

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.

--max-size

The new maximum size of the database expressed in bytes.

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

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

--zone-redundant -z

Specifies whether to enable zone redundancy.

accepted values: false, true