databases

Creates, updates, deletes, gets or lists a databases resource.

Overview

Name	databases
Type	Resource
Id	azure.sql.databases

Fields

The following fields are returned by SELECT queries:

get

Successfully retrieved the specified database.

Name	Datatype	Description
identity	object	The Azure Active Directory identity of the database.
kind	string	Kind of database. This is metadata used for the Azure portal experience.
location	string	Resource location.
managedBy	string	Resource that manages the database.
properties	object	Resource properties.
sku	object	The database SKU. The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or one of the following commands: azurecli<br /> az sql db list-editions -l &lt;location&gt; -o table<br /> ````<br /> <br /> powershell Get-AzSqlServerServiceObjective -Location <location> ````
tags	object	Resource tags.

list_by_elastic_pool

Successfully retrieved a list of databases in an elastic pool.

Name	Datatype	Description
identity	object	The Azure Active Directory identity of the database.
kind	string	Kind of database. This is metadata used for the Azure portal experience.
location	string	Resource location.
managedBy	string	Resource that manages the database.
properties	object	Resource properties.
sku	object	The database SKU. The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or one of the following commands: azurecli<br /> az sql db list-editions -l &lt;location&gt; -o table<br /> ````<br /> <br /> powershell Get-AzSqlServerServiceObjective -Location <location> ````
tags	object	Resource tags.

list_by_server

Successfully retrieved the list of databases.

Name	Datatype	Description
identity	object	The Azure Active Directory identity of the database.
kind	string	Kind of database. This is metadata used for the Azure portal experience.
location	string	Resource location.
managedBy	string	Resource that manages the database.
properties	object	Resource properties.
sku	object	The database SKU. The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API or one of the following commands: azurecli<br /> az sql db list-editions -l &lt;location&gt; -o table<br /> ````<br /> <br /> powershell Get-AzSqlServerServiceObjective -Location <location> ````
tags	object	Resource tags.

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get	select	resourceGroupName, serverName, databaseName, subscriptionId	$expand, $filter	Gets a database.
list_by_elastic_pool	select	resourceGroupName, serverName, elasticPoolName, subscriptionId		Gets a list of databases in an elastic pool.
list_by_server	select	resourceGroupName, serverName, subscriptionId	$skipToken	Gets a list of databases.
create_or_update	insert	resourceGroupName, serverName, databaseName, subscriptionId, data__location		Creates a new database or updates an existing database.
update	update	resourceGroupName, serverName, databaseName, subscriptionId		Updates an existing database.
delete	delete	resourceGroupName, serverName, databaseName, subscriptionId		Deletes the database.
export	exec	resourceGroupName, serverName, databaseName, subscriptionId, storageKeyType, storageKey, storageUri, administratorLogin, administratorLoginPassword		Exports a database.
failover	exec	resourceGroupName, serverName, databaseName, subscriptionId	replicaType	Failovers a database.
import	exec	resourceGroupName, serverName, databaseName, subscriptionId, storageKeyType, storageKey, storageUri, administratorLogin, administratorLoginPassword		Imports a bacpac into a new database.
rename	exec	resourceGroupName, serverName, databaseName, subscriptionId, id		Renames a database.
pause	exec	resourceGroupName, serverName, databaseName, subscriptionId		Pauses a database.
resume	exec	resourceGroupName, serverName, databaseName, subscriptionId		Resumes a database.
upgrade_data_warehouse	exec	resourceGroupName, serverName, databaseName, subscriptionId		Upgrades a data warehouse.

Parameters

Parameters can be passed in the WHERE clause of a query. Check the Methods section to see which parameters are required or optional for each operation.

Name	Datatype	Description
databaseName	string	The name of the database to be upgraded.
elasticPoolName	string	The name of the elastic pool.
resourceGroupName	string	The name of the resource group that contains the resource. You can obtain this value from the Azure Resource Manager API or the portal.
serverName	string	The name of the server.
subscriptionId	string	The subscription ID that identifies an Azure subscription.
$expand	string	The child resources to include in the response.
$filter	string	An OData filter expression that filters elements in the collection.
$skipToken	string
replicaType	string	The type of replica to be failed over.

SELECT examples

get

Gets a database.

SELECT
identity,
kind,
location,
managedBy,
properties,
sku,
tags
FROM azure.sql.databases
WHERE resourceGroupName = '{{ resourceGroupName }}' -- required
AND serverName = '{{ serverName }}' -- required
AND databaseName = '{{ databaseName }}' -- required
AND subscriptionId = '{{ subscriptionId }}' -- required
AND $expand = '{{ $expand }}'
AND $filter = '{{ $filter }}'
;

list_by_elastic_pool

Gets a list of databases in an elastic pool.

SELECT
identity,
kind,
location,
managedBy,
properties,
sku,
tags
FROM azure.sql.databases
WHERE resourceGroupName = '{{ resourceGroupName }}' -- required
AND serverName = '{{ serverName }}' -- required
AND elasticPoolName = '{{ elasticPoolName }}' -- required
AND subscriptionId = '{{ subscriptionId }}' -- required
;

list_by_server

Gets a list of databases.

SELECT
identity,
kind,
location,
managedBy,
properties,
sku,
tags
FROM azure.sql.databases
WHERE resourceGroupName = '{{ resourceGroupName }}' -- required
AND serverName = '{{ serverName }}' -- required
AND subscriptionId = '{{ subscriptionId }}' -- required
AND $skipToken = '{{ $skipToken }}'
;

INSERT examples

create_or_update

Creates a new database or updates an existing database.

INSERT INTO azure.sql.databases (
data__location,
data__tags,
data__sku,
data__identity,
data__properties,
resourceGroupName,
serverName,
databaseName,
subscriptionId
)
SELECT 
'{{ location }}' /* required */,
'{{ tags }}',
'{{ sku }}',
'{{ identity }}',
'{{ properties }}',
'{{ resourceGroupName }}',
'{{ serverName }}',
'{{ databaseName }}',
'{{ subscriptionId }}'
RETURNING
identity,
kind,
location,
managedBy,
properties,
sku,
tags
;

Manifest

# Description fields are for documentation purposes
- name: databases
  props:
    - name: resourceGroupName
      value: string
      description: Required parameter for the databases resource.
    - name: serverName
      value: string
      description: Required parameter for the databases resource.
    - name: databaseName
      value: string
      description: Required parameter for the databases resource.
    - name: subscriptionId
      value: string
      description: Required parameter for the databases resource.
    - name: location
      value: string
      description: |
        Resource location.
    - name: tags
      value: object
      description: |
        Resource tags.
    - name: sku
      value: object
      description: |
        The database SKU.
        The list of SKUs may vary by region and support offer. To determine the SKUs (including the SKU name, tier/edition, family, and capacity) that are available to your subscription in an Azure region, use the `Capabilities_ListByLocation` REST API or one of the following commands:
        ```azurecli
        az sql db list-editions -l <location> -o table

Get-AzSqlServerServiceObjective -Location <location>

• name: identity value: object description: | The Azure Active Directory identity of the database.
• name: properties value: object description: | Resource properties.

</TabItem>
</Tabs>


## `UPDATE` examples

<Tabs
    defaultValue="update"
    values={[
        { label: 'update', value: 'update' }
    ]}
>
<TabItem value="update">

Updates an existing database.

```sql
UPDATE azure.sql.databases
SET 
data__sku = '{{ sku }}',
data__identity = '{{ identity }}',
data__properties = '{{ properties }}',
data__tags = '{{ tags }}'
WHERE 
resourceGroupName = '{{ resourceGroupName }}' --required
AND serverName = '{{ serverName }}' --required
AND databaseName = '{{ databaseName }}' --required
AND subscriptionId = '{{ subscriptionId }}' --required
RETURNING
identity,
kind,
location,
managedBy,
properties,
sku,
tags;

DELETE examples

delete

Deletes the database.

DELETE FROM azure.sql.databases
WHERE resourceGroupName = '{{ resourceGroupName }}' --required
AND serverName = '{{ serverName }}' --required
AND databaseName = '{{ databaseName }}' --required
AND subscriptionId = '{{ subscriptionId }}' --required
;

Lifecycle Methods

export

Exports a database.

EXEC azure.sql.databases.export 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required 
@@json=
'{
"storageKeyType": "{{ storageKeyType }}", 
"storageKey": "{{ storageKey }}", 
"storageUri": "{{ storageUri }}", 
"administratorLogin": "{{ administratorLogin }}", 
"administratorLoginPassword": "{{ administratorLoginPassword }}", 
"authenticationType": "{{ authenticationType }}", 
"networkIsolation": "{{ networkIsolation }}"
}'
;

failover

Failovers a database.

EXEC azure.sql.databases.failover 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required, 
@replicaType='{{ replicaType }}'
;

import

Imports a bacpac into a new database.

EXEC azure.sql.databases.import 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required 
@@json=
'{
"storageKeyType": "{{ storageKeyType }}", 
"storageKey": "{{ storageKey }}", 
"storageUri": "{{ storageUri }}", 
"administratorLogin": "{{ administratorLogin }}", 
"administratorLoginPassword": "{{ administratorLoginPassword }}", 
"authenticationType": "{{ authenticationType }}", 
"networkIsolation": "{{ networkIsolation }}"
}'
;

rename

Renames a database.

EXEC azure.sql.databases.rename 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required 
@@json=
'{
"id": "{{ id }}"
}'
;

pause

Pauses a database.

EXEC azure.sql.databases.pause 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required
;

resume

Resumes a database.

EXEC azure.sql.databases.resume 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required
;

upgrade_data_warehouse

Upgrades a data warehouse.

EXEC azure.sql.databases.upgrade_data_warehouse 
@resourceGroupName='{{ resourceGroupName }}' --required, 
@serverName='{{ serverName }}' --required, 
@databaseName='{{ databaseName }}' --required, 
@subscriptionId='{{ subscriptionId }}' --required
;