Invoke-DbaDbUpgrade

Want to see the source code for this command? Check out Invoke-DbaDbUpgrade on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaDbUpgrade.

Synopsis

Take a database and upgrades it to compatibility of the SQL Instance its hosted on and updates the target recovery time to the new default of 60 seconds.

Description

Updates compatibility level and target recovery time, then runs CHECKDB with data_purity, DBCC updateusage, sp_updatestats and finally sp_refreshview against all user views.

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

The database(s) to process - this list is autopopulated from the server. If unspecified, you have to use -ExcludeDatabase to exclude some user databases or -AllUserDatabases to process all user
databases.

-ExcludeDatabase

The database(s) to exclude - this list is autopopulated from the server

-NoCheckDb

Skip checkdb

-NoUpdateUsage

Skip usage update

-NoUpdateStats

Skip stats update

-NoRefreshView

Skip view update

-AllUserDatabases

Run command against all user databases

-Force

Don't skip over databases that are already at the same level the instance is

-InputObject

A collection of databases (such as returned by Get-DbaDatabase)

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