Author | Garry Bargsley, blog.garrybargsley.com |
Availability | Windows, Linux, macOS |
Want to see the source code for this command? Check out Set-DbaDbCompatibility on GitHub.
Want to see the Bill Of Health for this command? Check out Set-DbaDbCompatibility.
Changes database compatibility levels to match SQL Server instance version or specified target level.
Updates database compatibility levels across one or more SQL Server instances. When no specific compatibility level is provided, automatically sets each database to match the SQL Server instance version it resides on. This is particularly useful after SQL Server upgrades when databases retain their original compatibility levels and need updating to take advantage of newer engine features and optimizations. The function processes only databases where the current compatibility level differs from the target level, making it safe to run repeatedly.
Set-DbaDbCompatibility
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Database] <String[]>]
[[-Compatibility] {Version60 | Version65 | Version70 | Version80 | Version90 | Version100 | Version110 | Version120 | Version130 | Version140 | Version150 | Version160 | Version170}]
[[-InputObject] <Database[]>]
[-EnableException]
[-WhatIf]
[-Confirm]
[<CommonParameters>]
PS C:\> Set-DbaDbCompatibility -SqlInstance sql2017a
Changes database compatibility level for all user databases on server sql2017a that have a Compatibility level that do not match
PS C:\> Set-DbaDbCompatibility -SqlInstance sql2019a -Compatibility Version150
Changes database compatibility level for all user databases on server sql2019a to Version150
PS C:\> Set-DbaDbCompatibility -SqlInstance sql2022b -Database Test -Compatibility Version160
Changes database compatibility level for database Test on server sql2022b to Version160
The target SQL Server instance or instances.
Alias | |
Required | False |
Pipeline | false |
Default Value |
SqlLogin to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance..
Alias | |
Required | False |
Pipeline | false |
Default Value |
Specifies which databases to update compatibility levels for. Accepts wildcards for pattern matching.
When omitted, processes all user databases on the target instance, excluding system databases.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Sets a specific target compatibility level for all processed databases. Must be a valid CompatibilityLevel enum value like Version160, Version150, etc.
When omitted, automatically updates each database to match its SQL Server instance version, which is typically desired after SQL Server upgrades.
Use this parameter when you need databases to remain at a specific compatibility level rather than matching the current server version.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Accepts database objects from the pipeline, typically from Get-DbaDatabase or other dbatools functions.
Use this when you need to apply compatibility level changes to a pre-filtered set of databases or when chaining multiple dbatools commands together.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Shows what would happen if the command were to run
Alias | wi |
Required | False |
Pipeline | false |
Default Value |
Prompts for confirmation of every step. For example:
Are you sure you want to perform this action?
Performing the operation "Update database" on target "pubs on SQL2016\VNEXT".
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "Y"):
Alias | cf |
Required | False |
Pipeline | false |
Default Value |