Invoke-DbaDbUpgrade

NAME Invoke-DbaDbUpgrade



SYNOPSIS

Take a database and upgrades it to compatibility of the SQL Instance its hosted on. Based on

https://thomaslarock.com/2014/06/upgrad ... -to-check/





SYNTAX

Invoke-DbaDbUpgrade [[-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-SqlCredential

<Pscredential>] [-Database <System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-NoCheckDb <Switch>]

[-NoUpdateUsage <Switch>] [-NoUpdateStats <Switch>] [-NoRefreshView <Switch>] [-AllUserDatabases <Switch>] [-Force

<Switch>] [-InputObject <Microsoft.SqlServer.Management.Smo.Database[]>] [-EnableException <Switch>]

[<CommonParameters>]





DESCRIPTION

Updates compatibility level, then runs CHECKDB with data_purity, DBCC updateusage, sp_updatestats and finally

sp_refreshview against all user views.





PARAMETERS

-AllUserDatabases [<Switch>]

Run command against all user databases



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-Database [<System.Object[]>]

The database(s) to process - this list is autopopulated from the server. If unspecified, all databases will be

processed.



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-EnableException [<Switch>]

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.



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-ExcludeDatabase [<System.Object[]>]

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



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-Force [<Switch>]

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



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-InputObject [<Microsoft.SqlServer.Management.Smo.Database[]>]

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



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-NoCheckDb [<Switch>]

Skip checkdb



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-NoRefreshView [<Switch>]

Skip view update



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-NoUpdateStats [<Switch>]

Skip stats update



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-NoUpdateUsage [<Switch>]

Skip usage update



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-SqlCredential [<Pscredential>]

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



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]

The target SQL Server instance or instances.



Required? false

Position? named

Default value

Accept pipeline input? False

Accept wildcard characters? false



<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,

ErrorAction, ErrorVariable, WarningAction, WarningVariable,

OutBuffer, PipelineVariable, and OutVariable. For more information, see

about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).



INPUTS



OUTPUTS



NOTES





Tags: Shrink, Database

Author: Stephen Bennett, https://sqlnotesfromtheunderground.wordpress.com/



Website: https://dbatools.io

Copyright: (c) 2018 by dbatools, licensed under MIT

License: MIT https://opensource.org/licenses/MIT



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

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



RELATED LINKS

https://dbatools.io/Invoke-DbaDbUpgrade