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 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
[--dest-resource-group]
[--dest-server]
[--elastic-pool]
[--no-wait]
[--service-objective]
[--tags]

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

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

Name of the elastic pool to create the new database in.

--no-wait

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

--service-objective

Name of the service objective for the new database.

--tags

Resource tags.

az sql db create

Create a database.

az sql db create --name
--resource-group
--server
[--collation]
[--edition]
[--elastic-pool]
[--max-size]
[--no-wait]
[--sample-name]
[--service-objective]
[--tags]

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

--collation

The collation of the database. If createMode is not Default, this value is ignored.

--edition

The edition of the database.

--elastic-pool

The name of the elastic pool the database is in. If elasticPoolName and requestedServiceObjectiveName are both updated, the value of requestedServiceObjectiveName is ignored. Not supported for DataWarehouse edition.

--max-size

The max storage size of the database. Only the following sizes are supported (in addition to limitations being placed on each edition): 100MB, 500MB, 1GB, 5GB, 10GB, 20GB, 30GB, 150GB, 200GB, 500GB. If no unit is specified, defaults to bytes (B).

--no-wait

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

--sample-name

Indicates the name of the sample schema to apply when creating this database. If createMode is not Default, this value is ignored. Not supported for DataWarehouse edition.

--service-objective

The name of the configured service level objective of the database. This is the service level objective that is in the process of being applied to the database. Once successfully updated, it will match the value of serviceLevelObjective property. To see possible values, query the capabilities API (/subscriptions/{subscriptionId}/providers/Microsoft.Sql/locations/{locationID}/capabilities) referred to by operationId: "Capabilities_ListByLocation.".

--tags

Resource tags.

az sql db delete

Delete a database.

az sql db delete --name
--resource-group
--server
[--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

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

The password of the SQL administrator.

--admin-user -u

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

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

--storage-key-type

The type of the storage key to use.

accepted values: SharedAccessKey, StorageAccessKey
--storage-uri

The storage uri to use.

Optional Parameters

--auth-type -a

The authentication type.

accepted values: ADPassword, SQL
default value: 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

The password of the SQL administrator.

--admin-user -u

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

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

--storage-key-type

The type of the storage key to use.

accepted values: SharedAccessKey, StorageAccessKey
--storage-uri

The storage uri to use.

Optional Parameters

--auth-type -a

The authentication type.

accepted values: ADPassword, SQL
default value: 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.

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
[--edition]
[--service-objective]
[--show-details {max-size}]

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

--edition

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

--service-objective

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

--show-details -d

List of additional details to include in output.

accepted values: max-size

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 restore

Create a new database by restoring from a backup.

az sql db restore --dest-name
--name
--resource-group
--server
[--deleted-time]
[--edition]
[--elastic-pool]
[--no-wait]
[--service-objective]
[--tags]
[--time]

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

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

The edition for the new database.

--elastic-pool

Name of the elastic pool to create the new database in.

--no-wait

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

--service-objective

Name of service objective for the new database.

--tags

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

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]
[--elastic-pool]
[--max-size]
[--no-wait]
[--remove]
[--service-objective]
[--set]

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

--elastic-pool

The name of the elastic pool to move the database into.

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