Invoke-DbaDbUpgrade

Synopsis

Upgrades database compatibility level and performs post-upgrade maintenance tasks

Description

Performs the essential steps needed after upgrading SQL Server or moving databases to a newer instance. Updates database compatibility level to match the hosting SQL Server version and sets target recovery time to 60 seconds for SQL Server 2016 and newer.

Executes critical post-upgrade maintenance including DBCC CHECKDB with DATA_PURITY to detect data corruption, DBCC UPDATEUSAGE to correct page counts, sp_updatestats to refresh statistics, and sp_refreshview to update all user views with new metadata. This automates the manual checklist DBAs typically follow after SQL Server upgrades to ensure databases function optimally on the new version.

Based on https://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/

Syntax

Invoke-DbaDbUpgrade
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [-SqlCredential <PSCredential>]
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-NoCheckDb]
    [-NoUpdateUsage]
    [-NoUpdateStats]
    [-NoRefreshView]
    [-AllUserDatabases]
    [-Force]
    [-InputObject <Database[]>]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Invoke-DbaDbUpgrade -SqlInstance PRD-SQL-MSD01 -Database Test

Runs the below processes against the databases
– Puts compatibility of database to level of SQL Instance
– Changes the target recovery time to the new default of 60 seconds (for SQL Server 2016 and newer)
– Runs CHECKDB DATA_PURITY
– Runs DBCC UPDATESUSAGE
– Updates all users statistics
– Runs sp_refreshview against every view in the database

Example: 2

PS C:\> Invoke-DbaDbUpgrade -SqlInstance PRD-SQL-INT01 -Database Test -NoRefreshView

Runs the upgrade command skipping the sp_refreshview update on all views

Example: 3

PS C:\> Invoke-DbaDbUpgrade -SqlInstance PRD-SQL-INT01 -Database Test -Force

If database Test is already at the correct compatibility, runs every necessary step

Example: 4

PS C:\> Get-DbaDatabase -SqlInstance sql2016 | Out-GridView -Passthru | Invoke-DbaDbUpgrade

Get only specific databases using GridView and pass those to Invoke-DbaDbUpgrade

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 upgrade and run post-upgrade maintenance tasks on. Accepts wildcards for pattern matching.
Use this when you need to target specific databases rather than processing all user databases on the instance.

-ExcludeDatabase

Excludes specific databases from the upgrade process when using -AllUserDatabases. Accepts wildcards for pattern matching.
Useful for skipping system-critical databases or those with special maintenance windows during bulk upgrade operations.

-NoCheckDb

Skips the DBCC CHECKDB with DATA_PURITY validation step during the upgrade process.
Use this when you’ve recently run integrity checks or need to reduce upgrade time, though this removes corruption detection from the process.

-NoUpdateUsage

Skips the DBCC UPDATEUSAGE step that corrects inaccuracies in page and row count information.
Use this when you’re confident space usage statistics are accurate or need to minimize upgrade time for very large databases.

-NoUpdateStats

Skips running sp_updatestats to refresh all user table statistics with current data distribution.
Use this when statistics were recently updated or when you have a separate statistics maintenance plan in place.

-NoRefreshView

Skips executing sp_refreshview on all user views to update their metadata for the new SQL Server version.
Use this when you have no views or prefer to refresh view metadata manually to avoid potential view compilation issues.

-AllUserDatabases

Processes all user databases on the instance, excluding system databases. Cannot be used with -Database parameter.
Use this for instance-wide upgrades after SQL Server version changes or when standardizing all databases to current compatibility levels.

-Force

Runs all maintenance tasks even on databases already at the correct compatibility level and target recovery time.
Use this when you need to ensure CHECKDB, UPDATEUSAGE, statistics updates, and view refreshes run regardless of compatibility status.

-InputObject

Accepts database objects from the pipeline, typically from Get-DbaDatabase output. Cannot be used with -Database or -AllUserDatabases.
Use this for targeted upgrades based on complex filtering criteria or when integrating with other dbatools commands in a pipeline.

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