Set-DbaDbCompatibility

Synopsis

Changes database compatibility levels to match SQL Server instance version or specified target level.

Description

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.

Syntax

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>]

Examples

Example: 1

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

Example: 2

PS C:\> Set-DbaDbCompatibility -SqlInstance sql2019a -Compatibility Version150

Changes database compatibility level for all user databases on server sql2019a to Version150

Example: 3

PS C:\> Set-DbaDbCompatibility -SqlInstance sql2022b -Database Test -Compatibility Version160

Changes database compatibility level for database Test on server sql2022b to Version160

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

-SqlCredential

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

-Database

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.

-Compatibility

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.

-InputObject

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.

-EnableException

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.

-WhatIf

Shows what would happen if the command were to run

-Confirm

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”):