Databases - Get

Gets a database.

GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}?api-version=2014-04-01
GET https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}?api-version=2014-04-01&$expand={$expand}

URI Parameters

Name In Required Type Description
databaseName
path True

string

The name of the database to be retrieved.

resourceGroupName
path True

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

string

The name of the server.

subscriptionId
path True

string

The subscription ID that identifies an Azure subscription.

api-version
query True

string

The API version to use for the request.

$expand
query

string

A comma separated list of child objects to expand in the response. Possible properties: serviceTierAdvisors, transparentDataEncryption.

Responses

Name Type Description
200 OK

Database

OK

Examples

Get a database

Sample request

GET https://management.azure.com/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-6852/providers/Microsoft.Sql/servers/sqlcrudtest-2080/databases/sqlcrudtest-9187?api-version=2014-04-01

Sample response

{
  "tags": {
    "tagKey1": "TagValue1"
  },
  "id": "/subscriptions/00000000-1111-2222-3333-444444444444/resourceGroups/sqlcrudtest-6852/providers/Microsoft.Sql/servers/sqlcrudtest-2080/databases/sqlcrudtest-9187",
  "name": "sqlcrudtest-9187",
  "type": "Microsoft.Sql/servers/databases",
  "location": "Japan East",
  "kind": "v12.0,user",
  "properties": {
    "databaseId": "200216b2-dc19-4866-85aa-f73fe4a1ebac",
    "edition": "Basic",
    "status": "Online",
    "serviceLevelObjective": "Basic",
    "collation": "SQL_Latin1_General_CP1_CI_AS",
    "maxSizeBytes": "2147483648",
    "creationDate": "2017-02-10T00:56:19.2Z",
    "currentServiceObjectiveId": "dd6d99bb-f193-4ec1-86f2-43d3bccbc49c",
    "requestedServiceObjectiveId": "dd6d99bb-f193-4ec1-86f2-43d3bccbc49c",
    "requestedServiceObjectiveName": "Basic",
    "sampleName": null,
    "defaultSecondaryLocation": "Japan West",
    "earliestRestoreDate": "2017-02-10T01:06:29.78Z",
    "elasticPoolName": null,
    "containmentState": 2,
    "readScale": "Disabled",
    "failoverGroupId": null
  }
}

Definitions

Name Description
CreateMode

Specifies the mode of database creation.

Default: regular database creation.

Copy: creates a database as a copy of an existing database. sourceDatabaseId must be specified as the resource ID of the source database.

OnlineSecondary/NonReadableSecondary: creates a database as a (readable or nonreadable) secondary replica of an existing database. sourceDatabaseId must be specified as the resource ID of the existing primary database.

PointInTimeRestore: Creates a database by restoring a point in time backup of an existing database. sourceDatabaseId must be specified as the resource ID of the existing database, and restorePointInTime must be specified.

Recovery: Creates a database by restoring a geo-replicated backup. sourceDatabaseId must be specified as the recoverable database resource ID to restore.

Restore: Creates a database by restoring a backup of a deleted database. sourceDatabaseId must be specified. If sourceDatabaseId is the database's original resource ID, then sourceDatabaseDeletionDate must be specified. Otherwise sourceDatabaseId must be the restorable dropped database resource ID and sourceDatabaseDeletionDate is ignored. restorePointInTime may also be specified to restore from an earlier point in time.

RestoreLongTermRetentionBackup: Creates a database by restoring from a long term retention vault. recoveryServicesRecoveryPointResourceId must be specified as the recovery point resource ID.

Copy, NonReadableSecondary, OnlineSecondary and RestoreLongTermRetentionBackup are not supported for DataWarehouse edition.

Database

Represents a database.

DatabaseEdition

The edition of the database. The DatabaseEditions enumeration contains all the valid editions. If createMode is NonReadableSecondary or OnlineSecondary, this value is ignored.

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:

az sql db list-editions -l <location> -o table
Get-AzSqlServerServiceObjective -Location <location>
OperationImpact

The estimated impact of doing recommended index action.

ReadScale

Conditional. If the database is a geo-secondary, readScale indicates whether read-only connections are allowed to this database or not. Not supported for DataWarehouse edition.

RecommendedIndex

The recommended indices for this database.

RecommendedIndexAction

The proposed index action. You can create a missing index, drop an unused index, or rebuild an existing index to improve its performance.

RecommendedIndexState

The current recommendation state.

RecommendedIndexType

The type of index (CLUSTERED, NONCLUSTERED, COLUMNSTORE, CLUSTERED COLUMNSTORE)

SampleName

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.

ServiceObjectiveName

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.

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:

az sql db list-editions -l <location> -o table
Get-AzSqlServerServiceObjective -Location <location>
ServiceTierAdvisor

The list of service tier advisors for this database. Expanded property

SloUsageMetric

Gets or sets serviceLevelObjectiveUsageMetrics for the service tier advisor.

TransparentDataEncryption

The transparent data encryption info for this database.

TransparentDataEncryptionStatus

The status of the database transparent data encryption.

CreateMode

Specifies the mode of database creation.

Default: regular database creation.

Copy: creates a database as a copy of an existing database. sourceDatabaseId must be specified as the resource ID of the source database.

OnlineSecondary/NonReadableSecondary: creates a database as a (readable or nonreadable) secondary replica of an existing database. sourceDatabaseId must be specified as the resource ID of the existing primary database.

PointInTimeRestore: Creates a database by restoring a point in time backup of an existing database. sourceDatabaseId must be specified as the resource ID of the existing database, and restorePointInTime must be specified.

Recovery: Creates a database by restoring a geo-replicated backup. sourceDatabaseId must be specified as the recoverable database resource ID to restore.

Restore: Creates a database by restoring a backup of a deleted database. sourceDatabaseId must be specified. If sourceDatabaseId is the database's original resource ID, then sourceDatabaseDeletionDate must be specified. Otherwise sourceDatabaseId must be the restorable dropped database resource ID and sourceDatabaseDeletionDate is ignored. restorePointInTime may also be specified to restore from an earlier point in time.

RestoreLongTermRetentionBackup: Creates a database by restoring from a long term retention vault. recoveryServicesRecoveryPointResourceId must be specified as the recovery point resource ID.

Copy, NonReadableSecondary, OnlineSecondary and RestoreLongTermRetentionBackup are not supported for DataWarehouse edition.

Name Type Description
Copy

string

Default

string

NonReadableSecondary

string

OnlineSecondary

string

PointInTimeRestore

string

Recovery

string

Restore

string

RestoreLongTermRetentionBackup

string

Database

Represents a database.

Name Type Description
id

string

Resource ID.

kind

string

Kind of database. This is metadata used for the Azure portal experience.

location

string

Resource location.

name

string

Resource name.

properties.collation

string

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

properties.containmentState

integer

The containment state of the database.

properties.createMode

CreateMode

Specifies the mode of database creation.

Default: regular database creation.

Copy: creates a database as a copy of an existing database. sourceDatabaseId must be specified as the resource ID of the source database.

OnlineSecondary/NonReadableSecondary: creates a database as a (readable or nonreadable) secondary replica of an existing database. sourceDatabaseId must be specified as the resource ID of the existing primary database.

PointInTimeRestore: Creates a database by restoring a point in time backup of an existing database. sourceDatabaseId must be specified as the resource ID of the existing database, and restorePointInTime must be specified.

Recovery: Creates a database by restoring a geo-replicated backup. sourceDatabaseId must be specified as the recoverable database resource ID to restore.

Restore: Creates a database by restoring a backup of a deleted database. sourceDatabaseId must be specified. If sourceDatabaseId is the database's original resource ID, then sourceDatabaseDeletionDate must be specified. Otherwise sourceDatabaseId must be the restorable dropped database resource ID and sourceDatabaseDeletionDate is ignored. restorePointInTime may also be specified to restore from an earlier point in time.

RestoreLongTermRetentionBackup: Creates a database by restoring from a long term retention vault. recoveryServicesRecoveryPointResourceId must be specified as the recovery point resource ID.

Copy, NonReadableSecondary, OnlineSecondary and RestoreLongTermRetentionBackup are not supported for DataWarehouse edition.

properties.creationDate

string

The creation date of the database (ISO8601 format).

properties.currentServiceObjectiveId

string

The current service level objective ID of the database. This is the ID of the service level objective that is currently active.

properties.databaseId

string

The ID of the database.

properties.defaultSecondaryLocation

string

The default secondary region for this database.

properties.earliestRestoreDate

string

This records the earliest start date and time that restore is available for this database (ISO8601 format).

properties.edition

DatabaseEdition

The edition of the database. The DatabaseEditions enumeration contains all the valid editions. If createMode is NonReadableSecondary or OnlineSecondary, this value is ignored.

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:

az sql db list-editions -l <location> -o table
Get-AzSqlServerServiceObjective -Location <location>
properties.elasticPoolName

string

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.

properties.failoverGroupId

string

The resource identifier of the failover group containing this database.

properties.maxSizeBytes

string

The max size of the database expressed in bytes. If createMode is not Default, this value is ignored. To see possible values, query the capabilities API (/subscriptions/{subscriptionId}/providers/Microsoft.Sql/locations/{locationID}/capabilities) referred to by operationId: "Capabilities_ListByLocation."

properties.readScale

ReadScale

Conditional. If the database is a geo-secondary, readScale indicates whether read-only connections are allowed to this database or not. Not supported for DataWarehouse edition.

properties.recommendedIndex

RecommendedIndex[]

The recommended indices for this database.

properties.recoveryServicesRecoveryPointResourceId

string

Conditional. If createMode is RestoreLongTermRetentionBackup, then this value is required. Specifies the resource ID of the recovery point to restore from.

properties.requestedServiceObjectiveId

string

The configured service level objective ID 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 currentServiceObjectiveId property. If requestedServiceObjectiveId and requestedServiceObjectiveName are both updated, the value of requestedServiceObjectiveId overrides the value of requestedServiceObjectiveName.

The list of SKUs may vary by region and support offer. To determine the service objective ids that are available to your subscription in an Azure region, use the Capabilities_ListByLocation REST API.

properties.requestedServiceObjectiveName

ServiceObjectiveName

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.

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:

az sql db list-editions -l <location> -o table
Get-AzSqlServerServiceObjective -Location <location>
properties.restorePointInTime

string

Conditional. If createMode is PointInTimeRestore, this value is required. If createMode is Restore, this value is optional. Specifies the point in time (ISO8601 format) 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.

properties.sampleName

SampleName

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.

properties.serviceLevelObjective

ServiceObjectiveName

The current service level objective of the database.

properties.serviceTierAdvisors

ServiceTierAdvisor[]

The list of service tier advisors for this database. Expanded property

properties.sourceDatabaseDeletionDate

string

Conditional. If createMode is Restore and sourceDatabaseId is the deleted database's original resource id when it existed (as opposed to its current restorable dropped database id), then this value is required. Specifies the time that the database was deleted.

properties.sourceDatabaseId

string

Conditional. If createMode is Copy, NonReadableSecondary, OnlineSecondary, PointInTimeRestore, Recovery, or Restore, then this value is required. Specifies the resource ID of the source database. If createMode is NonReadableSecondary or OnlineSecondary, the name of the source database must be the same as the new database being created.

properties.status

string

The status of the database.

properties.transparentDataEncryption

TransparentDataEncryption[]

The transparent data encryption info for this database.

properties.zoneRedundant

boolean

Whether or not this database is zone redundant, which means the replicas of this database will be spread across multiple availability zones.

tags

object

Resource tags.

type

string

Resource type.

DatabaseEdition

The edition of the database. The DatabaseEditions enumeration contains all the valid editions. If createMode is NonReadableSecondary or OnlineSecondary, this value is ignored.

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:

az sql db list-editions -l <location> -o table
Get-AzSqlServerServiceObjective -Location <location>
Name Type Description
Basic

string

Business

string

BusinessCritical

string

DataWarehouse

string

Free

string

GeneralPurpose

string

Hyperscale

string

Premium

string

PremiumRS

string

Standard

string

Stretch

string

System

string

System2

string

Web

string

OperationImpact

The estimated impact of doing recommended index action.

Name Type Description
changeValueAbsolute

number

The absolute impact to dimension.

changeValueRelative

number

The relative impact to dimension (null if not applicable)

name

string

The name of the impact dimension.

unit

string

The unit in which estimated impact to dimension is measured.

ReadScale

Conditional. If the database is a geo-secondary, readScale indicates whether read-only connections are allowed to this database or not. Not supported for DataWarehouse edition.

Name Type Description
Disabled

string

Enabled

string

RecommendedIndex

The recommended indices for this database.

Name Type Description
id

string

Resource ID.

name

string

Resource name.

properties.action

RecommendedIndexAction

The proposed index action. You can create a missing index, drop an unused index, or rebuild an existing index to improve its performance.

properties.columns

string[]

Columns over which to build index

properties.created

string

The UTC datetime showing when this resource was created (ISO8601 format).

properties.estimatedImpact

OperationImpact[]

The estimated impact of doing recommended index action.

properties.includedColumns

string[]

The list of column names to be included in the index

properties.indexScript

string

The full build index script

properties.indexType

RecommendedIndexType

The type of index (CLUSTERED, NONCLUSTERED, COLUMNSTORE, CLUSTERED COLUMNSTORE)

properties.lastModified

string

The UTC datetime of when was this resource last changed (ISO8601 format).

properties.reportedImpact

OperationImpact[]

The values reported after index action is complete.

properties.schema

string

The schema where table to build index over resides

properties.state

RecommendedIndexState

The current recommendation state.

properties.table

string

The table on which to build index.

type

string

Resource type.

RecommendedIndexAction

The proposed index action. You can create a missing index, drop an unused index, or rebuild an existing index to improve its performance.

Name Type Description
Create

string

Drop

string

Rebuild

string

RecommendedIndexState

The current recommendation state.

Name Type Description
Active

string

Blocked

string

Executing

string

Expired

string

Ignored

string

Pending

string

Pending Revert

string

Reverted

string

Reverting

string

Success

string

Verifying

string

RecommendedIndexType

The type of index (CLUSTERED, NONCLUSTERED, COLUMNSTORE, CLUSTERED COLUMNSTORE)

Name Type Description
CLUSTERED

string

CLUSTERED COLUMNSTORE

string

COLUMNSTORE

string

NONCLUSTERED

string

SampleName

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.

Name Type Description
AdventureWorksLT

string

ServiceObjectiveName

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.

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:

az sql db list-editions -l <location> -o table
Get-AzSqlServerServiceObjective -Location <location>
Name Type Description
Basic

string

DS100

string

DS1000

string

DS1200

string

DS1500

string

DS200

string

DS2000

string

DS300

string

DS400

string

DS500

string

DS600

string

DW100

string

DW1000

string

DW10000c

string

DW1000c

string

DW1200

string

DW1500

string

DW15000c

string

DW1500c

string

DW200

string

DW2000

string

DW2000c

string

DW2500c

string

DW300

string

DW3000

string

DW30000c

string

DW3000c

string

DW400

string

DW500

string

DW5000c

string

DW600

string

DW6000

string

DW6000c

string

DW7500c

string

ElasticPool

string

Free

string

P1

string

P11

string

P15

string

P2

string

P3

string

P4

string

P6

string

PRS1

string

PRS2

string

PRS4

string

PRS6

string

S0

string

S1

string

S12

string

S2

string

S3

string

S4

string

S6

string

S7

string

S9

string

System

string

System0

string

System1

string

System2

string

System2L

string

System3

string

System3L

string

System4

string

System4L

string

ServiceTierAdvisor

The list of service tier advisors for this database. Expanded property

Name Type Description
id

string

Resource ID.

name

string

Resource name.

properties.activeTimeRatio

number

The activeTimeRatio for service tier advisor.

properties.avgDtu

number

Gets or sets avgDtu for service tier advisor.

properties.confidence

number

Gets or sets confidence for service tier advisor.

properties.currentServiceLevelObjective

string

Gets or sets currentServiceLevelObjective for service tier advisor.

properties.currentServiceLevelObjectiveId

string

Gets or sets currentServiceLevelObjectiveId for service tier advisor.

properties.databaseSizeBasedRecommendationServiceLevelObjective

string

Gets or sets databaseSizeBasedRecommendationServiceLevelObjective for service tier advisor.

properties.databaseSizeBasedRecommendationServiceLevelObjectiveId

string

Gets or sets databaseSizeBasedRecommendationServiceLevelObjectiveId for service tier advisor.

properties.disasterPlanBasedRecommendationServiceLevelObjective

string

Gets or sets disasterPlanBasedRecommendationServiceLevelObjective for service tier advisor.

properties.disasterPlanBasedRecommendationServiceLevelObjectiveId

string

Gets or sets disasterPlanBasedRecommendationServiceLevelObjectiveId for service tier advisor.

properties.maxDtu

number

Gets or sets maxDtu for service tier advisor.

properties.maxSizeInGB

number

Gets or sets maxSizeInGB for service tier advisor.

properties.minDtu

number

Gets or sets minDtu for service tier advisor.

properties.observationPeriodEnd

string

The observation period start (ISO8601 format).

properties.observationPeriodStart

string

The observation period start (ISO8601 format).

properties.overallRecommendationServiceLevelObjective

string

Gets or sets overallRecommendationServiceLevelObjective for service tier advisor.

properties.overallRecommendationServiceLevelObjectiveId

string

Gets or sets overallRecommendationServiceLevelObjectiveId for service tier advisor.

properties.serviceLevelObjectiveUsageMetrics

SloUsageMetric[]

Gets or sets serviceLevelObjectiveUsageMetrics for the service tier advisor.

properties.usageBasedRecommendationServiceLevelObjective

string

Gets or sets usageBasedRecommendationServiceLevelObjective for service tier advisor.

properties.usageBasedRecommendationServiceLevelObjectiveId

string

Gets or sets usageBasedRecommendationServiceLevelObjectiveId for service tier advisor.

type

string

Resource type.

SloUsageMetric

Gets or sets serviceLevelObjectiveUsageMetrics for the service tier advisor.

Name Type Description
inRangeTimeRatio

number

Gets or sets inRangeTimeRatio for SLO usage metric.

serviceLevelObjective

ServiceObjectiveName

The serviceLevelObjective for SLO usage metric.

serviceLevelObjectiveId

string

The serviceLevelObjectiveId for SLO usage metric.

TransparentDataEncryption

The transparent data encryption info for this database.

Name Type Description
id

string

Resource ID.

location

string

Resource location.

name

string

Resource name.

properties.status

TransparentDataEncryptionStatus

The status of the database transparent data encryption.

type

string

Resource type.

TransparentDataEncryptionStatus

The status of the database transparent data encryption.

Name Type Description
Disabled

string

Enabled

string