LLI Wiki — My Digital Knowledge Base

SMO PowerShell: change compatibility level and database owner

Change CompatibilityLevel and SetOwner on a SQL Server database with SMO, then Alter().

Change the compatibility level and the owner of a SQL Server database from PowerShell with SMO (SQL Server Management Objects).

Prerequisite: the SMO assemblies must be loaded, see Load the SMO assemblies in Windows PowerShell. For SMO usage examples, see Using SMO with PowerShell to obtain SQL Server job information.

Change compatibility level and owner

$NewDatabase is an SMO Database object retrieved beforehand (for example from the Databases collection of a Server object). The change is only sent to the server by Alter().

echo "Changing CompatibilityLevel..."
if ($NewDatabase.CompatibilityLevel -ne [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version160) {
    $NewDatabase.CompatibilityLevel = [Microsoft.SqlServer.Management.Smo.CompatibilityLevel]::Version160
}

echo "Changing Owner to 'sa'..."
$NewDatabase.SetOwner("sa")

echo "Applying change..."
$NewDatabase.Alter()

Version160 corresponds to compatibility level 160 (SQL Server 2022).

Mastodon LLIMastodon SocialFosstodon