SQL Databases - 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 Updates a database's auditing policy.
az sql db copy Creates a copy of an existing database.
az sql db create Creates a database.
az sql db delete Deletes a database or data warehouse.
az sql db export Exports a database to a bacpac.
az sql db import Imports a bacpac into an existing database.
az sql db list Lists all databases and data warehouses in a server, or all databases in an elastic pool.
az sql db list-editions Shows database editions that are available for your subscription.
az sql db list-usages Returns database usages.
az sql db replica Manage replication between databases.
az sql db replica create Creates a database as a readable secondary replica of an existing database.
az sql db replica delete-link Permanently stops data replication between two database replicas.
az sql db replica list-links Lists the replicas of a database and corresponding replication status.
az sql db replica set-primary Sets which replica database is primary by failing over from the current primary replica database.
az sql db restore Creates a new database by restoring from a database backup.
az sql db show Gets a database or data warehouse.
az sql db threat-policy Manage a database's threat detection policy.
az sql db threat-policy show Gets a database's threat detection policy.
az sql db threat-policy update Updates a database's threat detection policy.
az sql db update Updates a database.

az sql db copy

Creates a copy of an existing database.

az sql db copy --dest-name
--name
--resource-group
--server
[--dest-resource-group]
[--dest-server]
[--elastic-pool]
[--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=.

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

--service-objective

Name of the service objective for the new database.

--tags

Resource tags.

az sql db create

Creates a database.

az sql db create --name
--resource-group
--server
[--collation]
[--edition]
[--elastic-pool]
[--max-size]
[--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=.

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

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

Deletes a database or data warehouse.

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

--server -s

Name of the Azure SQL server.

Optional Parameters

--yes -y

Do not prompt for confirmation.

az sql db export

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

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

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

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

The authentication type.

accepted values: ADPassword, SQL
default value: SQL

az sql db list

Lists all databases and data warehouses in a server, or all databases in an 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=.

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

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

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

--server -s

Name of the Azure SQL server.

az sql db restore

Creates a new database by restoring from a database backup.

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

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

--server -s

Name of the Azure SQL server.

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

Optional Parameters

--edition

The edition for the new database.

--elastic-pool

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

--service-objective

Name of service objective for the new database.

--tags

Resource tags.

az sql db show

Gets a database or data warehouse.

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

--server -s

Name of the Azure SQL server.

az sql db update

Updates a database.

az sql db update --name
--resource-group
--server
[--add]
[--elastic-pool]
[--max-size]
[--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=.

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

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

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