managed_databases

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

Overview

Name	managed_databases
Type	Resource
Id	azure.sql.managed_databases

Fields

The following fields are returned by SELECT queries:

get

Name	Datatype	Description
id	string	Fully qualified resource ID for the resource. Ex - /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/{resourceProviderNamespace}/{resourceType}/{resourceName}.
name	string	The name of the resource.
autoCompleteRestore	boolean	Whether to auto complete restore of this managed database.
catalogCollation	string	Collation of the metadata catalog. Known values are: "DATABASE_DEFAULT" and "SQL_Latin1_General_CP1_CI_AS". (DATABASE_DEFAULT, SQL_Latin1_General_CP1_CI_AS)
collation	string	Collation of the managed database.
createMode	string	Managed database create mode. PointInTimeRestore: Create a database by restoring a point in time backup of an existing database. SourceDatabaseName, SourceManagedInstanceName and PointInTime must be specified. RestoreExternalBackup: Create a database by restoring from external backup files. Collation, StorageContainerUri and StorageContainerSasToken must be specified. Recovery: Creates a database by restoring a geo-replicated backup. RecoverableDatabaseId must be specified as the recoverable database resource ID to restore. RestoreLongTermRetentionBackup: Create a database by restoring from a long term retention backup (longTermRetentionBackupResourceId required). Known values are: "Default", "RestoreExternalBackup", "PointInTimeRestore", "Recovery", and "RestoreLongTermRetentionBackup". (Default, RestoreExternalBackup, PointInTimeRestore, Recovery, RestoreLongTermRetentionBackup)
creationDate	string (date-time)	Creation date of the database.
crossSubscriptionRestorableDroppedDatabaseId	string	The restorable cross-subscription dropped database resource id to restore when creating this database.
crossSubscriptionSourceDatabaseId	string	The resource identifier of the cross-subscription source database associated with create operation of this database.
crossSubscriptionTargetManagedInstanceId	string	Target managed instance id used in cross-subscription restore.
defaultSecondaryLocation	string	Geo paired region.
earliestRestorePoint	string (date-time)	Earliest restore point in time for point in time restore.
extendedAccessibilityInfo	object	Additional observability and troubleshooting information for databases in ‘Inaccessible’ state.
failoverGroupId	string	Instance Failover Group resource identifier that this managed database belongs to.
isLedgerOn	boolean	Whether or not this database is a ledger database, which means all tables in the database are ledger tables. Note: the value of this property cannot be changed after the database has been created.
lastBackupName	string	Last backup file name for restore of this managed database.
location	string	The geo-location where the resource lives. Required.
longTermRetentionBackupResourceId	string	The name of the Long Term Retention backup to be used for restore of this managed database.
recoverableDatabaseId	string	The resource identifier of the recoverable database associated with create operation of this database.
restorableDroppedDatabaseId	string	The restorable dropped database resource id to restore when creating this database.
restorePointInTime	string (date-time)	Conditional. If createMode is PointInTimeRestore, this value is required. Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database.
sourceDatabaseId	string	The resource identifier of the source database associated with create operation of this database.
status	string	Status of the database. Known values are: "Online", "Offline", "Shutdown", "Creating", "Inaccessible", "Restoring", "Updating", "Stopping", "Stopped", "Starting", "DbMoving", and "DbCopying". (Online, Offline, Shutdown, Creating, Inaccessible, Restoring, Updating, Stopping, Stopped, Starting, DbMoving, DbCopying)
storageContainerIdentity	string	Conditional. If createMode is RestoreExternalBackup, this value is used. Specifies the identity used for storage container authentication. Can be 'SharedAccessSignature' or 'ManagedIdentity'; if not specified 'SharedAccessSignature' is assumed.
storageContainerSasToken	string	Conditional. If createMode is RestoreExternalBackup and storageContainerIdentity is not ManagedIdentity, this value is required. Specifies the storage container sas token.
storageContainerUri	string	Conditional. If createMode is RestoreExternalBackup, this value is required. Specifies the uri of the storage container where backups for this restore are stored.
systemData	object	Azure Resource Manager metadata containing createdBy and modifiedBy information.
tags	object	Resource tags.
type	string	The type of the resource. E.g. "Microsoft.Compute/virtualMachines" or "Microsoft.Storage/storageAccounts".

list_by_instance

Name	Datatype	Description
id	string	Fully qualified resource ID for the resource. Ex - /subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/{resourceProviderNamespace}/{resourceType}/{resourceName}.
name	string	The name of the resource.
autoCompleteRestore	boolean	Whether to auto complete restore of this managed database.
catalogCollation	string	Collation of the metadata catalog. Known values are: "DATABASE_DEFAULT" and "SQL_Latin1_General_CP1_CI_AS". (DATABASE_DEFAULT, SQL_Latin1_General_CP1_CI_AS)
collation	string	Collation of the managed database.
createMode	string	Managed database create mode. PointInTimeRestore: Create a database by restoring a point in time backup of an existing database. SourceDatabaseName, SourceManagedInstanceName and PointInTime must be specified. RestoreExternalBackup: Create a database by restoring from external backup files. Collation, StorageContainerUri and StorageContainerSasToken must be specified. Recovery: Creates a database by restoring a geo-replicated backup. RecoverableDatabaseId must be specified as the recoverable database resource ID to restore. RestoreLongTermRetentionBackup: Create a database by restoring from a long term retention backup (longTermRetentionBackupResourceId required). Known values are: "Default", "RestoreExternalBackup", "PointInTimeRestore", "Recovery", and "RestoreLongTermRetentionBackup". (Default, RestoreExternalBackup, PointInTimeRestore, Recovery, RestoreLongTermRetentionBackup)
creationDate	string (date-time)	Creation date of the database.
crossSubscriptionRestorableDroppedDatabaseId	string	The restorable cross-subscription dropped database resource id to restore when creating this database.
crossSubscriptionSourceDatabaseId	string	The resource identifier of the cross-subscription source database associated with create operation of this database.
crossSubscriptionTargetManagedInstanceId	string	Target managed instance id used in cross-subscription restore.
defaultSecondaryLocation	string	Geo paired region.
earliestRestorePoint	string (date-time)	Earliest restore point in time for point in time restore.
extendedAccessibilityInfo	object	Additional observability and troubleshooting information for databases in ‘Inaccessible’ state.
failoverGroupId	string	Instance Failover Group resource identifier that this managed database belongs to.
isLedgerOn	boolean	Whether or not this database is a ledger database, which means all tables in the database are ledger tables. Note: the value of this property cannot be changed after the database has been created.
lastBackupName	string	Last backup file name for restore of this managed database.
location	string	The geo-location where the resource lives. Required.
longTermRetentionBackupResourceId	string	The name of the Long Term Retention backup to be used for restore of this managed database.
recoverableDatabaseId	string	The resource identifier of the recoverable database associated with create operation of this database.
restorableDroppedDatabaseId	string	The restorable dropped database resource id to restore when creating this database.
restorePointInTime	string (date-time)	Conditional. If createMode is PointInTimeRestore, this value is required. Specifies the point in time (ISO8601 format) of the source database that will be restored to create the new database.
sourceDatabaseId	string	The resource identifier of the source database associated with create operation of this database.
status	string	Status of the database. Known values are: "Online", "Offline", "Shutdown", "Creating", "Inaccessible", "Restoring", "Updating", "Stopping", "Stopped", "Starting", "DbMoving", and "DbCopying". (Online, Offline, Shutdown, Creating, Inaccessible, Restoring, Updating, Stopping, Stopped, Starting, DbMoving, DbCopying)
storageContainerIdentity	string	Conditional. If createMode is RestoreExternalBackup, this value is used. Specifies the identity used for storage container authentication. Can be 'SharedAccessSignature' or 'ManagedIdentity'; if not specified 'SharedAccessSignature' is assumed.
storageContainerSasToken	string	Conditional. If createMode is RestoreExternalBackup and storageContainerIdentity is not ManagedIdentity, this value is required. Specifies the storage container sas token.
storageContainerUri	string	Conditional. If createMode is RestoreExternalBackup, this value is required. Specifies the uri of the storage container where backups for this restore are stored.
systemData	object	Azure Resource Manager metadata containing createdBy and modifiedBy information.
tags	object	Resource tags.
type	string	The type of the resource. E.g. "Microsoft.Compute/virtualMachines" or "Microsoft.Storage/storageAccounts".

Methods

The following methods are available for this resource:

Name	Accessible by	Required Params	Optional Params	Description
get	select	resource_group_name, managed_instance_name, database_name, subscription_id		Gets a managed database.
list_by_instance	select	resource_group_name, managed_instance_name, subscription_id		Gets a list of managed databases.
create_or_update	insert	resource_group_name, managed_instance_name, database_name, subscription_id, location		Creates a new database or updates an existing database.
update	update	resource_group_name, managed_instance_name, database_name, subscription_id		Updates an existing database.
create_or_update	replace	resource_group_name, managed_instance_name, database_name, subscription_id, location		Creates a new database or updates an existing database.
delete	delete	resource_group_name, managed_instance_name, database_name, subscription_id		Deletes a managed database.
list_inaccessible_by_instance	exec	resource_group_name, managed_instance_name, subscription_id		Gets a list of inaccessible managed databases in a managed instance.
cancel_move	exec	resource_group_name, managed_instance_name, database_name, subscription_id, destinationManagedDatabaseId		Cancels a managed database move operation.
complete_move	exec	resource_group_name, managed_instance_name, database_name, subscription_id, destinationManagedDatabaseId		Completes a managed database move operation.
complete_restore	exec	resource_group_name, managed_instance_name, database_name, subscription_id, lastBackupName		Completes the restore operation on a managed database.
reevaluate_inaccessible_database_state	exec	resource_group_name, managed_instance_name, database_name, subscription_id		Reevaluates the inaccessibility state of a managed database.
start_move	exec	resource_group_name, managed_instance_name, database_name, subscription_id, destinationManagedDatabaseId		Starts a managed database move operation.

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
database_name	string	The name of the database. Required.
managed_instance_name	string	The name of the managed instance. Required.
resource_group_name	string	The name of the resource group. The name is case insensitive. Required.
subscription_id	string

SELECT examples

get

Gets a managed database.

SELECT
id,
name,
autoCompleteRestore,
catalogCollation,
collation,
createMode,
creationDate,
crossSubscriptionRestorableDroppedDatabaseId,
crossSubscriptionSourceDatabaseId,
crossSubscriptionTargetManagedInstanceId,
defaultSecondaryLocation,
earliestRestorePoint,
extendedAccessibilityInfo,
failoverGroupId,
isLedgerOn,
lastBackupName,
location,
longTermRetentionBackupResourceId,
recoverableDatabaseId,
restorableDroppedDatabaseId,
restorePointInTime,
sourceDatabaseId,
status,
storageContainerIdentity,
storageContainerSasToken,
storageContainerUri,
systemData,
tags,
type
FROM azure.sql.managed_databases
WHERE resource_group_name = '{{ resource_group_name }}' -- required
AND managed_instance_name = '{{ managed_instance_name }}' -- required
AND database_name = '{{ database_name }}' -- required
AND subscription_id = '{{ subscription_id }}' -- required
;

list_by_instance

Gets a list of managed databases.

SELECT
id,
name,
autoCompleteRestore,
catalogCollation,
collation,
createMode,
creationDate,
crossSubscriptionRestorableDroppedDatabaseId,
crossSubscriptionSourceDatabaseId,
crossSubscriptionTargetManagedInstanceId,
defaultSecondaryLocation,
earliestRestorePoint,
extendedAccessibilityInfo,
failoverGroupId,
isLedgerOn,
lastBackupName,
location,
longTermRetentionBackupResourceId,
recoverableDatabaseId,
restorableDroppedDatabaseId,
restorePointInTime,
sourceDatabaseId,
status,
storageContainerIdentity,
storageContainerSasToken,
storageContainerUri,
systemData,
tags,
type
FROM azure.sql.managed_databases
WHERE resource_group_name = '{{ resource_group_name }}' -- required
AND managed_instance_name = '{{ managed_instance_name }}' -- required
AND subscription_id = '{{ subscription_id }}' -- required
;

INSERT examples

create_or_update

Creates a new database or updates an existing database.

INSERT INTO azure.sql.managed_databases (
tags,
location,
properties,
resource_group_name,
managed_instance_name,
database_name,
subscription_id
)
SELECT 
'{{ tags }}',
'{{ location }}' /* required */,
'{{ properties }}',
'{{ resource_group_name }}',
'{{ managed_instance_name }}',
'{{ database_name }}',
'{{ subscription_id }}'
RETURNING
id,
name,
location,
properties,
systemData,
tags,
type
;

Manifest

# Description fields are for documentation purposes
- name: managed_databases
props:
  - name: resource_group_name
    value: "{{ resource_group_name }}"
    description: Required parameter for the managed_databases resource.
  - name: managed_instance_name
    value: "{{ managed_instance_name }}"
    description: Required parameter for the managed_databases resource.
  - name: database_name
    value: "{{ database_name }}"
    description: Required parameter for the managed_databases resource.
  - name: subscription_id
    value: "{{ subscription_id }}"
    description: Required parameter for the managed_databases resource.
  - name: tags
    value: "{{ tags }}"
    description: |
      Resource tags.
  - name: location
    value: "{{ location }}"
    description: |
      The geo-location where the resource lives. Required.
  - name: properties
    description: |
      Resource properties.
    value:
      collation: "{{ collation }}"
      status: "{{ status }}"
      creationDate: "{{ creationDate }}"
      earliestRestorePoint: "{{ earliestRestorePoint }}"
      restorePointInTime: "{{ restorePointInTime }}"
      defaultSecondaryLocation: "{{ defaultSecondaryLocation }}"
      catalogCollation: "{{ catalogCollation }}"
      createMode: "{{ createMode }}"
      storageContainerUri: "{{ storageContainerUri }}"
      sourceDatabaseId: "{{ sourceDatabaseId }}"
      crossSubscriptionSourceDatabaseId: "{{ crossSubscriptionSourceDatabaseId }}"
      restorableDroppedDatabaseId: "{{ restorableDroppedDatabaseId }}"
      crossSubscriptionRestorableDroppedDatabaseId: "{{ crossSubscriptionRestorableDroppedDatabaseId }}"
      storageContainerIdentity: "{{ storageContainerIdentity }}"
      storageContainerSasToken: "{{ storageContainerSasToken }}"
      failoverGroupId: "{{ failoverGroupId }}"
      recoverableDatabaseId: "{{ recoverableDatabaseId }}"
      longTermRetentionBackupResourceId: "{{ longTermRetentionBackupResourceId }}"
      autoCompleteRestore: {{ autoCompleteRestore }}
      lastBackupName: "{{ lastBackupName }}"
      crossSubscriptionTargetManagedInstanceId: "{{ crossSubscriptionTargetManagedInstanceId }}"
      isLedgerOn: {{ isLedgerOn }}
      extendedAccessibilityInfo:
        inaccessibilityReasonErrorCode: "{{ inaccessibilityReasonErrorCode }}"
        inaccessibilityReasonDescription: "{{ inaccessibilityReasonDescription }}"
        inaccessibilityReasonKind: "{{ inaccessibilityReasonKind }}"
        inaccessibilityReasonTdeKeyUri: "{{ inaccessibilityReasonTdeKeyUri }}"

UPDATE examples

update

Updates an existing database.

UPDATE azure.sql.managed_databases
SET 
properties = '{{ properties }}',
tags = '{{ tags }}'
WHERE 
resource_group_name = '{{ resource_group_name }}' --required
AND managed_instance_name = '{{ managed_instance_name }}' --required
AND database_name = '{{ database_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
RETURNING
id,
name,
location,
properties,
systemData,
tags,
type;

REPLACE examples

create_or_update

Creates a new database or updates an existing database.

REPLACE azure.sql.managed_databases
SET 
tags = '{{ tags }}',
location = '{{ location }}',
properties = '{{ properties }}'
WHERE 
resource_group_name = '{{ resource_group_name }}' --required
AND managed_instance_name = '{{ managed_instance_name }}' --required
AND database_name = '{{ database_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
AND location = '{{ location }}' --required
RETURNING
id,
name,
location,
properties,
systemData,
tags,
type;

DELETE examples

delete

Deletes a managed database.

DELETE FROM azure.sql.managed_databases
WHERE resource_group_name = '{{ resource_group_name }}' --required
AND managed_instance_name = '{{ managed_instance_name }}' --required
AND database_name = '{{ database_name }}' --required
AND subscription_id = '{{ subscription_id }}' --required
;

Lifecycle Methods

list_inaccessible_by_instance

Gets a list of inaccessible managed databases in a managed instance.

EXEC azure.sql.managed_databases.list_inaccessible_by_instance 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required
;

cancel_move

Cancels a managed database move operation.

EXEC azure.sql.managed_databases.cancel_move 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@database_name='{{ database_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required 
@@json=
'{
"destinationManagedDatabaseId": "{{ destinationManagedDatabaseId }}"
}'
;

complete_move

Completes a managed database move operation.

EXEC azure.sql.managed_databases.complete_move 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@database_name='{{ database_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required 
@@json=
'{
"destinationManagedDatabaseId": "{{ destinationManagedDatabaseId }}"
}'
;

complete_restore

Completes the restore operation on a managed database.

EXEC azure.sql.managed_databases.complete_restore 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@database_name='{{ database_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required 
@@json=
'{
"lastBackupName": "{{ lastBackupName }}"
}'
;

reevaluate_inaccessible_database_state

Reevaluates the inaccessibility state of a managed database.

EXEC azure.sql.managed_databases.reevaluate_inaccessible_database_state 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@database_name='{{ database_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required
;

start_move

Starts a managed database move operation.

EXEC azure.sql.managed_databases.start_move 
@resource_group_name='{{ resource_group_name }}' --required, 
@managed_instance_name='{{ managed_instance_name }}' --required, 
@database_name='{{ database_name }}' --required, 
@subscription_id='{{ subscription_id }}' --required 
@@json=
'{
"destinationManagedDatabaseId": "{{ destinationManagedDatabaseId }}", 
"operationMode": "{{ operationMode }}"
}'
;