Databases - Get

Reference

Service:: SQL Database

API Version:: 2014-04-01

Gets a database.

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

With optional parameters:

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

HTTP

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

Status code:: 200

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

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

Share via