< Back
Update-DbaInstance
Post
NAME Update-DbaInstance
SYNOPSIS
Invokes installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.
SYNTAX
Update-DbaInstance [[-ComputerName] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-Credential
<Pscredential>] [-Version <System.String[]>] [-Type <System.String[]>] [-InstanceName <String>] [-Path
<System.String[]>] [-Restart <Switch>] [-Continue <Switch>] [-Throttle <Int>] [-Authentication <String>]
[-ExtractPath <String>] [-EnableException <Switch>] [<CommonParameters>]
Update-DbaInstance [[-ComputerName] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-Credential
<Pscredential>] -KB <System.String[]> [-InstanceName <String>] [-Path <System.String[]>] [-Restart <Switch>]
[-Continue <Switch>] [-Throttle <Int>] [-Authentication <String>] [-ExtractPath <String>] [-EnableException
<Switch>] [<CommonParameters>]
DESCRIPTION
Starts and automated process of updating SQL Server installation to a specific version defined in the parameters.
The command will:
* Search for SQL Server installations in a remote registry
* Check if current settings are applicable to the current SQL Server versions
* Search for a KB executable in a folder specified in -Path
* Establish a PSRemote connection to the target machine if necessary
* Extract KB to a temporary folder in a current user's profile
* Run the installation from the temporary folder updating all instances on the computer at once
* Remove temporary files
* Restart the computer (if -Restart is specified)
* Repeat for each consequent KB and computer
The impact of this function is set to High, if you don't want to receive interactive prompts, set -Confirm to
$false.
Credentials are a required parameter for remote machines. Without specifying -Credential, the installation will
fail due to lack of permissions.
CredSSP is a recommended transport for running the updates remotely. Update-DbaInstance will attempt to reconfigure
local and remote hosts to support CredSSP, which is why it is desirable to run this command in an elevated console
at all times.
CVE-2018-0886 security update is required for both local and remote hosts. If CredSSP connections are failing,
make sure to
apply recent security updates prior to doing anything else.
Always backup databases and configurations prior to upgrade.
PARAMETERS
-Authentication [<String>]
Chooses an authentication protocol for remote connections.
If the protocol fails to establish a connection
Defaults:
* CredSSP when -Credential is specified - due to the fact that repository Path is usually a network share and
credentials need to be passed to the remote host
to avoid the double-hop issue.
* Default when -Credential is not specified. Will likely fail if a network path is specified.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ComputerName [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
Target computer with SQL instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Continue [<Switch>]
Continues a failed installation attempt when specified. Will abort a previously failed installation otherwise.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Credential [<Pscredential>]
Windows Credential with permission to log on to the remote server.
Must be specified for any remote connection if update Repository is located on a network folder.
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
-ExtractPath [<String>]
Lets you specify a location to extract the update file to on the system requiring the update. e.g. C:\\temp
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InstanceName [<String>]
Only updates a specific instance(s).
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KB [<System.String[]>]
Install a specific update or list of updates. Can be a number of a string KBXXXXXXX.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<System.String[]>]
Path to the folder(s) with SQL Server patches downloaded. It will be scanned recursively for available patches.
Path should be available from both server with SQL Server installation and client that runs the command.
All file names should match the pattern used by Microsoft: SQLServer####*-KB###-*x##*.exe
If a file is missing in the repository, the installation will fail.
Consider setting the following configuration if you want to omit this parameter: `Set-DbatoolsConfig -Name
Path.SQLServerUpdates -Value '\\\\path\\to\\updates'`
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Restart [<Switch>]
Restart computer automatically after a successful installation of a patch and wait until it comes back online.
Using this parameter is the only way to chain-install more than 1 patch on a computer, since every single
patch will require a restart of said computer.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Throttle [<Int>]
Maximum number of computers updated in parallel. Once reached, the update operations will queue up.
Default: 50
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<System.String[]>]
Type of the update: All | ServicePack | CumulativeUpdate.
Default: All
Use -Version to limit upgrade to a certain Major version of SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Version [<System.String[]>]
A target version of the installation you want to reach. If not specified, a latest available version would be
used by default.
Can be defined using the following general pattern: <MajorVersion><SPX><CUX>.
Any part of the pattern can be omitted if needed:
2008R2SP1 - will update SQL 2008R2 to SP1
2016CU3 - will update SQL 2016 to CU3 of current Service Pack installed
SP0CU3 - will update all existing SQL Server versions to RTM CU3 without installing any service packs
SP1CU7 - will update all existing SQL Server versions to SP1 and then (after restart if -Restart is specified)
to SP1CU7
CU7 - will update all existing SQL Server versions to CU7 of current Service Pack installed
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: Install, Patching, SP, CU, Instance
Author: Kirill Kravtsov (@nvarscar) https://nvarscar.wordpress.com/
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Requires Local Admin rights on destination computer(s).
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1 -Version SP3 -Path \\\\network\\share
Updates all applicable SQL Server installations on SQL1 to SP3.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Prompts for confirmation before the update.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1, SQL2 -Restart -Path \\\\network\\share -Confirm:$false
Updates all applicable SQL Server installations on SQL1 and SQL2 with the most recent patch.
It will install latest ServicePack, restart the computers, install latest Cumulative Update, and finally restart
the computer once again.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1 -Version 2012 -Type ServicePack -Path \\\\network\\share
Updates SQL Server 2012 on SQL1 with the most recent ServicePack found in your patch repository.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Prompts for confirmation before the update.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1 -KB 123456 -Restart -Path \\\\network\\share -Confirm:$false
Installs KB 123456 on SQL1 and restarts the computer.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Update-DbaInstance -ComputerName Server1 -Version SQL2012SP3, SQL2016SP2CU3 -Path \\\\network\\share -Restart
-Confirm:$false
Updates SQL 2012 to SP3 and SQL 2016 to SP2CU3 on Server1. Each update will be followed by a restart.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Update-DbaInstance -ComputerName Server1 -Path \\\\network\\share -Restart -Confirm:$false -ExtractPath
"C:\\temp"
Updates all applicable SQL Server installations on Server1 with the most recent patch. Each update will be
followed by a restart.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
Extracts the files in local driver on Server1 C:\\temp.
RELATED LINKS
SYNOPSIS
Invokes installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.
SYNTAX
Update-DbaInstance [[-ComputerName] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-Credential
<Pscredential>] [-Version <System.String[]>] [-Type <System.String[]>] [-InstanceName <String>] [-Path
<System.String[]>] [-Restart <Switch>] [-Continue <Switch>] [-Throttle <Int>] [-Authentication <String>]
[-ExtractPath <String>] [-EnableException <Switch>] [<CommonParameters>]
Update-DbaInstance [[-ComputerName] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>] [-Credential
<Pscredential>] -KB <System.String[]> [-InstanceName <String>] [-Path <System.String[]>] [-Restart <Switch>]
[-Continue <Switch>] [-Throttle <Int>] [-Authentication <String>] [-ExtractPath <String>] [-EnableException
<Switch>] [<CommonParameters>]
DESCRIPTION
Starts and automated process of updating SQL Server installation to a specific version defined in the parameters.
The command will:
* Search for SQL Server installations in a remote registry
* Check if current settings are applicable to the current SQL Server versions
* Search for a KB executable in a folder specified in -Path
* Establish a PSRemote connection to the target machine if necessary
* Extract KB to a temporary folder in a current user's profile
* Run the installation from the temporary folder updating all instances on the computer at once
* Remove temporary files
* Restart the computer (if -Restart is specified)
* Repeat for each consequent KB and computer
The impact of this function is set to High, if you don't want to receive interactive prompts, set -Confirm to
$false.
Credentials are a required parameter for remote machines. Without specifying -Credential, the installation will
fail due to lack of permissions.
CredSSP is a recommended transport for running the updates remotely. Update-DbaInstance will attempt to reconfigure
local and remote hosts to support CredSSP, which is why it is desirable to run this command in an elevated console
at all times.
CVE-2018-0886 security update is required for both local and remote hosts. If CredSSP connections are failing,
make sure to
apply recent security updates prior to doing anything else.
Always backup databases and configurations prior to upgrade.
PARAMETERS
-Authentication [<String>]
Chooses an authentication protocol for remote connections.
If the protocol fails to establish a connection
Defaults:
* CredSSP when -Credential is specified - due to the fact that repository Path is usually a network share and
credentials need to be passed to the remote host
to avoid the double-hop issue.
* Default when -Credential is not specified. Will likely fail if a network path is specified.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ComputerName [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
Target computer with SQL instance or instances.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Continue [<Switch>]
Continues a failed installation attempt when specified. Will abort a previously failed installation otherwise.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Credential [<Pscredential>]
Windows Credential with permission to log on to the remote server.
Must be specified for any remote connection if update Repository is located on a network folder.
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
-ExtractPath [<String>]
Lets you specify a location to extract the update file to on the system requiring the update. e.g. C:\\temp
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InstanceName [<String>]
Only updates a specific instance(s).
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-KB [<System.String[]>]
Install a specific update or list of updates. Can be a number of a string KBXXXXXXX.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<System.String[]>]
Path to the folder(s) with SQL Server patches downloaded. It will be scanned recursively for available patches.
Path should be available from both server with SQL Server installation and client that runs the command.
All file names should match the pattern used by Microsoft: SQLServer####*-KB###-*x##*.exe
If a file is missing in the repository, the installation will fail.
Consider setting the following configuration if you want to omit this parameter: `Set-DbatoolsConfig -Name
Path.SQLServerUpdates -Value '\\\\path\\to\\updates'`
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Restart [<Switch>]
Restart computer automatically after a successful installation of a patch and wait until it comes back online.
Using this parameter is the only way to chain-install more than 1 patch on a computer, since every single
patch will require a restart of said computer.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Throttle [<Int>]
Maximum number of computers updated in parallel. Once reached, the update operations will queue up.
Default: 50
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<System.String[]>]
Type of the update: All | ServicePack | CumulativeUpdate.
Default: All
Use -Version to limit upgrade to a certain Major version of SQL Server.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Version [<System.String[]>]
A target version of the installation you want to reach. If not specified, a latest available version would be
used by default.
Can be defined using the following general pattern: <MajorVersion><SPX><CUX>.
Any part of the pattern can be omitted if needed:
2008R2SP1 - will update SQL 2008R2 to SP1
2016CU3 - will update SQL 2016 to CU3 of current Service Pack installed
SP0CU3 - will update all existing SQL Server versions to RTM CU3 without installing any service packs
SP1CU7 - will update all existing SQL Server versions to SP1 and then (after restart if -Restart is specified)
to SP1CU7
CU7 - will update all existing SQL Server versions to CU7 of current Service Pack installed
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: Install, Patching, SP, CU, Instance
Author: Kirill Kravtsov (@nvarscar) https://nvarscar.wordpress.com/
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
Requires Local Admin rights on destination computer(s).
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1 -Version SP3 -Path \\\\network\\share
Updates all applicable SQL Server installations on SQL1 to SP3.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Prompts for confirmation before the update.
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1, SQL2 -Restart -Path \\\\network\\share -Confirm:$false
Updates all applicable SQL Server installations on SQL1 and SQL2 with the most recent patch.
It will install latest ServicePack, restart the computers, install latest Cumulative Update, and finally restart
the computer once again.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1 -Version 2012 -Type ServicePack -Path \\\\network\\share
Updates SQL Server 2012 on SQL1 with the most recent ServicePack found in your patch repository.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Prompts for confirmation before the update.
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Update-DbaInstance -ComputerName SQL1 -KB 123456 -Restart -Path \\\\network\\share -Confirm:$false
Installs KB 123456 on SQL1 and restarts the computer.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Update-DbaInstance -ComputerName Server1 -Version SQL2012SP3, SQL2016SP2CU3 -Path \\\\network\\share -Restart
-Confirm:$false
Updates SQL 2012 to SP3 and SQL 2016 to SP2CU3 on Server1. Each update will be followed by a restart.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
-------------------------- EXAMPLE 6 --------------------------
PS C:\\>Update-DbaInstance -ComputerName Server1 -Path \\\\network\\share -Restart -Confirm:$false -ExtractPath
"C:\\temp"
Updates all applicable SQL Server installations on Server1 with the most recent patch. Each update will be
followed by a restart.
Binary files for the update will be searched among all files and folders recursively in \\\\network\\share.
Does not prompt for confirmation.
Extracts the files in local driver on Server1 C:\\temp.
RELATED LINKS