Note
Access to this page requires authorization. You can try signing in or .
Access to this page requires authorization. You can try .
Set-AzSqlDatabase
- Module:
- Az.Sql Module
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
-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
-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
-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
-ComputeGeneration
The compute generation to assign.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | Family |
Parameter sets
-ComputeModel
Computed model of Azure Sql database. Serverless or Provisioned
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-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
-DatabaseName
Specifies the name of the database.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | Name |
Parameter sets
-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
-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
-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
-EncryptionProtector
The encryption protector key for SQL Database.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-EncryptionProtectorAutoRotation
The AKV Key Auto Rotation status
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-FederatedClientId
The federated client id for the SQL Database. It is used for cross tenant CMK scenario.
Parameter properties
| Type: | |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-FreeLimitExhaustionBehavior
Exhaustion behavior of free limit database.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-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
-KeyList
The list of AKV keys for the SQL Database.
Parameter properties
| Type: | String[] |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-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
-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
-MaintenanceConfigurationId
The Maintenance configuration id for the SQL Database.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-ManualCutover
Use Manual Cutover for migrating to Hyperscale.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-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
-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
-NewName
The new name to rename the database to.
Parameter properties
| Type: | String |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-PerformCutover
Trigger Cutover for migrating to Hyperscale.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-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
-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
-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
-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
-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
-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
-Tags
Key-value pairs in the form of a hash table. For example: @{key0="value0";key1=$null;key2="value2"}
Parameter properties
| Type: | Hashtable |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
| Aliases: | Tag |
Parameter sets
-UseFreeLimit
Use free limit on this database.
Parameter properties
| Type: | SwitchParameter |
| Default value: | None |
| Supports wildcards: | False |
| DontShow: | False |
Parameter sets
-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
-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
-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
-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
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.
Inputs
String
Outputs
AzureSqlDatabaseModel
Related Links
Azure PowerShell
Feedback
Was this page helpful?