commands

^

Invoke-DbaDbUpgrade

Author Stephen Bennett, sqlnotesfromtheunderground.wordpress.com
Availability Windows, Linux, macOS

 

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.

Alias
Required False
Pipeline false
Default Value
-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..

Alias
Required False
Pipeline false
Default Value
-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.

Alias
Required False
Pipeline false
Default Value
-ExcludeDatabase

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

Alias
Required False
Pipeline false
Default Value
-NoCheckDb

Skip checkdb

Alias
Required False
Pipeline false
Default Value False
-NoUpdateUsage

Skip usage update

Alias
Required False
Pipeline false
Default Value False
-NoUpdateStats

Skip stats update

Alias
Required False
Pipeline false
Default Value False
-NoRefreshView

Skip view update

Alias
Required False
Pipeline false
Default Value False
-AllUserDatabases

Run command against all user databases

Alias
Required False
Pipeline false
Default Value False
-Force

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

Alias
Required False
Pipeline false
Default Value False
-InputObject

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

Alias
Required False
Pipeline true (ByValue)
Default Value
-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.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run

Alias wi
Required False
Pipeline false
Default Value
-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"):

Alias cf
Required False
Pipeline false
Default Value