Set-AzSqlDatabase
Sets properties for a database, or moves an existing database into an elastic pool.
Syntax
Update (Default)
Set-AzSqlDatabase
[-DatabaseName] <String>
[-ServerName] <String>
[-ResourceGroupName] <String>
[-MaxSizeBytes <Int64>]
[-Edition <String>]
[-RequestedServiceObjectiveName <String>]
[-ElasticPoolName <String>]
[-ReadScale <DatabaseReadScale>]
[-Tags <Hashtable>]
[-ZoneRedundant]
[-AsJob]
[-LicenseType <String>]
[-ComputeModel <String>]
[-AutoPauseDelayInMinutes <Int32>]
[-MinimumCapacity <Double>]
[-HighAvailabilityReplicaCount <Int32>]
[-BackupStorageRedundancy <String>]
[-SecondaryType <String>]
[-MaintenanceConfigurationId <String>]
[-AssignIdentity]
[-EncryptionProtector <String>]
[-UserAssignedIdentityId <String[]>]
[-KeyList <String[]>]
[-KeysToRemove <String[]>]
[-FederatedClientId <Guid>]
[-PreferredEnclaveType <String>]
[-EncryptionProtectorAutoRotation]
[-UseFreeLimit]
[-FreeLimitExhaustionBehavior <String>]
[-ManualCutover]
[-PerformCutover]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
VcoreBasedDatabase
Set-AzSqlDatabase
[-DatabaseName] <String>
[-ServerName] <String>
[-ResourceGroupName] <String>
[-MaxSizeBytes <Int64>]
[-Edition <String>]
[-ReadScale <DatabaseReadScale>]
[-Tags <Hashtable>]
[-ZoneRedundant]
[-AsJob]
[-VCore <Int32>]
[-ComputeGeneration <String>]
[-LicenseType <String>]
[-ComputeModel <String>]
[-AutoPauseDelayInMinutes <Int32>]
[-MinimumCapacity <Double>]
[-HighAvailabilityReplicaCount <Int32>]
[-BackupStorageRedundancy <String>]
[-SecondaryType <String>]
[-MaintenanceConfigurationId <String>]
[-AssignIdentity]
[-EncryptionProtector <String>]
[-UserAssignedIdentityId <String[]>]
[-KeyList <String[]>]
[-KeysToRemove <String[]>]
[-FederatedClientId <Guid>]
[-PreferredEnclaveType <String>]
[-EncryptionProtectorAutoRotation]
[-UseFreeLimit]
[-FreeLimitExhaustionBehavior <String>]
[-ManualCutover]
[-PerformCutover]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
Rename
Set-AzSqlDatabase
[-DatabaseName] <String>
[-ServerName] <String>
[-ResourceGroupName] <String>
-NewName <String>
[-AsJob]
[-BackupStorageRedundancy <String>]
[-SecondaryType <String>]
[-MaintenanceConfigurationId <String>]
[-AssignIdentity]
[-EncryptionProtector <String>]
[-UserAssignedIdentityId <String[]>]
[-KeyList <String[]>]
[-KeysToRemove <String[]>]
[-FederatedClientId <Guid>]
[-PreferredEnclaveType <String>]
[-EncryptionProtectorAutoRotation]
[-UseFreeLimit]
[-FreeLimitExhaustionBehavior <String>]
[-ManualCutover]
[-PerformCutover]
[-DefaultProfile <IAzureContextContainer>]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
Description
The Set-AzSqlDatabase cmdlet sets properties for a database in Azure SQL Database. This cmdlet can modify the service tier (Edition ), performance level (RequestedServiceObjectiveName ), and storage max size (MaxSizeBytes ) for the database. In addition, you can specify the ElasticPoolName parameter to move a database into an elastic pool. If a database is already in an elastic pool, you can use the RequestedServiceObjectiveName parameter to move the database out of an elastic pool and into a performance level for single databases.
Examples
Example 1: Update a database to a Standard S0 database
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Standard" -RequestedServiceObjectiveName "S0"
ResourceGroupName : ResourceGroup01
ServerName : Server01
DatabaseName : Database01
Location : Central US
DatabaseId : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition : Standard
CollationName : SQL_Latin1_General_CP1_CI_AS
CatalogCollation :
MaxSizeBytes : 268435456000
Status : Online
CreationDate : 7/3/2015 7:33:37 AM
CurrentServiceObjectiveId : 455330e1-00cd-488b-b5fa-177c226f28b7
CurrentServiceObjectiveName : S0
RequestedServiceObjectiveId : 455330e1-00cd-488b-b5fa-177c226f28b7
RequestedServiceObjectiveName :
ElasticPoolName :
EarliestRestoreDate :
Tags :
This command updates a database named Database01 to a Standard S0 database on a server named Server01.
Example 2: Add a database to an elastic pool
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -ElasticPoolName "ElasticPool01"
ResourceGroupName : ResourceGroup01
ServerName : Server01
DatabaseName : Database01
Location : Central US
DatabaseId : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition : Standard
CollationName : SQL_Latin1_General_CP1_CI_AS
CatalogCollation :
MaxSizeBytes : 268435456000
Status : Online
CreationDate : 7/3/2015 7:33:37 AM
CurrentServiceObjectiveId : d1737d22-a8ea-4de7-9bd0-33395d2a7419
CurrentServiceObjectiveName : ElasticPool
RequestedServiceObjectiveId : d1737d22-a8ea-4de7-9bd0-33395d2a7419
RequestedServiceObjectiveName :
ElasticPoolName : elasticpool01
EarliestRestoreDate :
Tags :
This command adds a database named Database01 to the elastic pool named ElasticPool01 hosted on the server named Server01.
Example 3: Modify the storage max size of a database
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -MaxSizeBytes 1099511627776
ResourceGroupName : ResourceGroup01
ServerName : Server01
DatabaseName : Database01
Location : Central US
DatabaseId : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition : Standard
CollationName : SQL_Latin1_General_CP1_CI_AS
CatalogCollation :
MaxSizeBytes : 1099511627776
Status : Online
CreationDate : 8/24/2017 9:00:37 AM
CurrentServiceObjectiveId : 789681b8-ca10-4eb0-bdf2-e0b050601b40
CurrentServiceObjectiveName : S3
RequestedServiceObjectiveId : 789681b8-ca10-4eb0-bdf2-e0b050601b40
RequestedServiceObjectiveName :
ElasticPoolName :
EarliestRestoreDate :
Tags :
This command updates a database named Database01 to set its max size to 1 TB.
Example 4: Update a existing General Purpose database to Hyperscale service tier
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -Edition "Hyperscale" -RequestedServiceObjectiveName "HS_Gen5_2"
ResourceGroupName : ResourceGroup01
ServerName : Server01
DatabaseName : Database01
Location : Central US
DatabaseId : 56246136-839f-4171-80af-4c28142463b1
Edition : Hyperscale
CollationName : SQL_Latin1_General_CP1_CI_AS
CatalogCollation :
MaxSizeBytes : -1
Status : Online
CreationDate : 12/6/2020 5:34:16 PM
CurrentServiceObjectiveId : 00000000-0000-0000-0000-000000000000
CurrentServiceObjectiveName : HS_Gen5_2
RequestedServiceObjectiveName : HS_Gen5_2
RequestedServiceObjectiveId :
ElasticPoolName :
EarliestRestoreDate : 12/6/2020 5:34:16 PM
Tags : {}
ResourceId : /subscriptions/xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxx/resourceGroups/ResourceGroup01/providers/Microsoft.Sql/servers/Server01/databases/Database01
CreateMode :
ReadScale : Enabled
ZoneRedundant :
Capacity : 2
Family : Gen5
SkuName : HS_Gen5
LicenseType : LicenseIncluded
AutoPauseDelayInMinutes :
MinimumCapacity :
ReadReplicaCount : 1
BackupStorageRedundancy : Geo
This command updates a database named Database01 from General Purpose to Hyperscale service tier.
Example 5: Update the preferred enclave type of a database to VBS
Set-AzSqlDatabase -ResourceGroupName "ResourceGroup01" -DatabaseName "Database01" -ServerName "Server01" -PreferredEnclaveType "VBS"
ResourceGroupName : ResourceGroup01
ServerName : Server01
DatabaseName : Database01
Location : Central US
DatabaseId : a1e6bd1a-735a-4d48-8b98-afead5ef1218
Edition : Standard
CollationName : SQL_Latin1_General_CP1_CI_AS
CatalogCollation :
MaxSizeBytes : 1099511627776
Status : Online
CreationDate : 8/24/2017 9:00:37 AM
CurrentServiceObjectiveId : 789681b8-ca10-4eb0-bdf2-e0b050601b40
CurrentServiceObjectiveName : S3
RequestedServiceObjectiveId : 789681b8-ca10-4eb0-bdf2-e0b050601b40
PreferredEnclaveType : VBS
RequestedServiceObjectiveName :
ElasticPoolName :
EarliestRestoreDate :
Tags :
This command updates a database to configure VBS enclave on it
Parameters
-AsJob
Run cmdlet in the background
Parameter properties
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-AssignIdentity
Generate and assign a Microsoft Entra identity for this database for use with key management services like Azure KeyVault.
Parameter properties
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-AutoPauseDelayInMinutes
The auto pause delay in minutes for database (serverless only), -1 to opt out
Parameter properties
Type: Int32
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-BackupStorageRedundancy
The Backup storage redundancy used to store backups for the SQL Database. Options are: Local, Zone, Geo and GeoZone. To know the options supported by each edition of the database, see Get-AzSqlCapability .
Parameter properties
Type: String
Default value: None
Accepted values: Local, Zone, Geo, GeoZone
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ComputeGeneration
The compute generation to assign.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Aliases: Family
Parameter sets
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ComputeModel
Computed model of Azure Sql database. Serverless or Provisioned
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-Confirm
Prompts you for confirmation before running the cmdlet.
Parameter properties
Type: SwitchParameter
Default value: False
Supports wildcards: False
DontShow: False
Aliases: cf
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-DatabaseName
Specifies the name of the database.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Aliases: Name
Parameter sets
(All)
Position: 2
Mandatory: True
Value from pipeline: False
Value from pipeline by property name: True
Value from remaining arguments: False
-DefaultProfile
The credentials, account, tenant, and subscription used for communication with azure
Parameter properties
Type: IAzureContextContainer
Default value: None
Supports wildcards: False
DontShow: False
Aliases: AzContext, AzureRmContext, AzureCredential
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-Edition
Specifies the edition for the database.
The acceptable values for this parameter are:
None
Basic
Standard
Premium
DataWarehouse
Free
Stretch
GeneralPurpose
Hyperscale
BusinessCritical
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ElasticPoolName
Specifies name of the elastic pool in which to move the database.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-EncryptionProtector
The encryption protector key for SQL Database.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-EncryptionProtectorAutoRotation
The AKV Key Auto Rotation status
Parameter properties
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: True
Value from remaining arguments: False
-FederatedClientId
The federated client id for the SQL Database. It is used for cross tenant CMK scenario.
Parameter properties
Type: Nullable<T> [ Guid ]
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-FreeLimitExhaustionBehavior
Exhaustion behavior of free limit database.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-HighAvailabilityReplicaCount
The number of readonly secondary replicas associated with the database. For Hyperscale edition only.
Parameter properties
Type: Int32
Default value: None
Supports wildcards: False
DontShow: False
Aliases: ReadReplicaCount
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-KeyList
The list of AKV keys for the SQL Database.
Parameter properties
Type: String [ ]
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-KeysToRemove
The list of AKV keys to remove from the SQL Database.
Parameter properties
Type: String [ ]
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-LicenseType
The license type for the Azure Sql database. Possible values are:
BasePrice - Azure Hybrid Benefit (AHB) discounted pricing for existing SQL Server license owners is applied. Database price will be discounted for existing SQL Server license owners.
LicenseIncluded - Azure Hybrid Benefit (AHB) discount pricing for existing SQL Server license owners is not applied. Database price will include a new SQL Server license costs.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-MaintenanceConfigurationId
The Maintenance configuration id for the SQL Database.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ManualCutover
Use Manual Cutover for migrating to Hyperscale.
Parameter properties
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-MaxSizeBytes
The maximum size of the Azure SQL Database in bytes.
Parameter properties
Type: Int64
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-MinimumCapacity
The Minimal capacity that database will always have allocated, if not paused.
For serverless Azure Sql databases only.
Parameter properties
Type: Double
Default value: None
Supports wildcards: False
DontShow: False
Aliases: MinVCore, MinCapacity
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-NewName
The new name to rename the database to.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Rename
Position: Named
Mandatory: True
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
Trigger Cutover for migrating to Hyperscale.
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-PreferredEnclaveType
The preferred enclave type for the Azure Sql database. Possible values are Default and VBS.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ReadScale
If enabled, connections that have application intent set to readonly in their connection string may be routed to a readonly secondary replica. This property is only settable for Premium and Business Critical databases.
Parameter properties
Type: DatabaseReadScale
Default value: None
Accepted values: Disabled, Enabled
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-RequestedServiceObjectiveName
Specifies the name of the service objective to assign to the database. For information about
service objectives, see Azure SQL Database Service Tiers and Performance Levels
in the Microsoft Developer Network Library.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ResourceGroupName
Specifies the name of resource group to which the server is assigned.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: 0
Mandatory: True
Value from pipeline: False
Value from pipeline by property name: True
Value from remaining arguments: False
-SecondaryType
The secondary type of the database if it is a secondary. Valid values are Geo and Named.
Parameter properties
Type: String
Default value: None
Accepted values: Named, Geo
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ServerName
Specifies the name of the server that hosts the database.
Parameter properties
Type: String
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: 1
Mandatory: True
Value from pipeline: False
Value from pipeline by property name: True
Value from remaining arguments: False
Key-value pairs in the form of a hash table. For example:
@{key0="value0";key1=$null;key2="value2"}
Type: Hashtable
Default value: None
Supports wildcards: False
DontShow: False
Aliases: Tag
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-UseFreeLimit
Use free limit on this database.
Parameter properties
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-UserAssignedIdentityId
The list of user assigned identity for the SQL Database.
Parameter properties
Type: String [ ]
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-VCore
The Vcore number for the Azure Sql database
Parameter properties
Type: Int32
Default value: None
Supports wildcards: False
DontShow: False
Aliases: Capacity, MaxVCore, MaxCapacity
Parameter sets
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-WhatIf
Shows what would happen if the cmdlet runs.
The cmdlet is not run.
Parameter properties
Type: SwitchParameter
Default value: False
Supports wildcards: False
DontShow: False
Aliases: wi
Parameter sets
(All)
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
-ZoneRedundant
The zone redundancy to associate with the Azure Sql Database
Parameter properties
Type: SwitchParameter
Default value: None
Supports wildcards: False
DontShow: False
Parameter sets
Update
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
VcoreBasedDatabase
Position: Named
Mandatory: False
Value from pipeline: False
Value from pipeline by property name: False
Value from remaining arguments: False
CommonParameters
This cmdlet supports the common parameters: -Debug, -ErrorAction, -ErrorVariable,
-InformationAction, -InformationVariable, -OutBuffer, -OutVariable, -PipelineVariable,
-ProgressAction, -Verbose, -WarningAction, and -WarningVariable. For more information, see
about_CommonParameters .
Outputs