SQL Server Database engine patching
This script can install Service pack, security patch and Cumulative update on SQL instance(Database Engine). This script is created with the help of PowerShell form. So when this executes, a Form pops-up where user need to provide some mandatory info.
First field(Execution Type): This filed contains 2 options.
a. Install patch: This option can be used when user only need to install patch.
b. Perform prerequisites, install patch: This option can be used when want to perform pre-requisites prior to patch installation. As part of prerequisites, this script Databases backup, creates copy of system databases and place them in provided location and it also fetch version and service account details and place them into a provided path.
Second Field(Backup Type): Here user need to provide the type of backup. If user has configured backup via third party tool than user have to choose "Skip backup" option. If backup is configured via SQL agent then user has to provide Job name in script.
Third Filed(Prerequisites Location): Here user have to provide location where he\she need to keep all system DB's copy, Version detail and service account detail.
Fourth Field(Binary File Location): Here user have to provide patch file location.
After completion of script, a in-built Summary while will pop-up with the status of patch installation.
Note: This script can install patch on single server server at once.
#----------------- Loading Required assebmlies -------------------#
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')|Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |Out-Null
[System.Windows.Forms.Application]::EnableVisualStyles()
#------- Declaring variables ---------#
$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') localhost
$version = $server.Information.Version.Major
$global:Binary_File_Loc = $null
$global:Pre_req_Folder_Path = $null
$global:Execution_type = $null
$global:Backup_type = $null
$global:OK_Button_value = $null
#------- Function to create Form ---------#
Function Get-Form{
$Font = New-Object System.Drawing.Font("Arial",8,[System.Drawing.FontStyle]::Bold)
$Form = New-Object system.Windows.Forms.Form
$Form.Text = "Patching Tool"
#----- Form item Decalartion -----#
$Exec_type_Label = New-Object System.Windows.Forms.Label
$Backup_label = New-object System.Windows.Forms.Label
$P_Loc_label = New-Object System.Windows.Forms.Label
$Binary_loc_label = New-Object System.Windows.Forms.Label
$Ok_Button = New-object System.Windows.Forms.button
$Cancel_Button = New-object System.Windows.Forms.button
$Fetch_P_loc_Button = New-object System.Windows.Forms.button
$Fetch_Binary_F_Button = New-object System.Windows.Forms.button
$P_Loc_TextBox = New-Object System.Windows.Forms.TextBox
$Binary_F_loc_TextBox = New-Object System.Windows.Forms.TextBox
$Opt1_Radio_button = New-object System.Windows.Forms.RadioButton
$Opt2_Radio_button = New-object System.Windows.Forms.RadioButton
$Backup_DropDown = new-object System.Windows.Forms.ComboBox
$Form.Height = 500
$form.Width = 600
#------ Labels Defination -------#
$Exec_type_Label.Text = " Execution Type:"
$Exec_type_Label.AutoSize = $True
$Exec_type_Label.Font = $Font
$Exec_type_Label.Location = New-Object System.Drawing.point(25,40)
$Backup_label.Text = " Backup Type:"
$Backup_label.AutoSize = $True
$Backup_label.Font = $Font
$Backup_label.Location = New-Object System.Drawing.point(25,110)
$P_Loc_label.Text = " Pre-requisites Location:"
$P_Loc_label.AutoSize = $True
$P_Loc_label.Font = $Font
$P_Loc_label.Location = New-Object System.Drawing.point(25,170)
$Binary_loc_label.Text = " Binary File Location:"
$Binary_loc_label.AutoSize = $True
$Binary_loc_label.Font = $Font
$Binary_loc_label.Location = New-Object System.Drawing.point(25,230)
#------ Radio Buttons Defination -------#
$Opt1_Radio_button.Text = "Perform pre-requisites, install patch"
$Opt1_Radio_button.Autosize = $True
$Opt1_Radio_button.Location = New-Object System.Drawing.point(205,40)
$Opt1_Radio_button.Add_Click({if($Opt1_Radio_button.checked)
{$P_Loc_TextBox.Enabled = $True
$Fetch_P_loc_Button.Enabled = $True
$Backup_DropDown.Enabled = $True}})
$Opt2_Radio_button.Text = "Install patch"
$Opt2_Radio_button.Autosize = $True
$Opt2_Radio_button.Location = New-Object System.Drawing.point(205,60)
$Opt2_Radio_button.Add_Click({if($Opt2_Radio_button.checked)
{$P_Loc_TextBox.Enabled = $false
$Fetch_P_loc_Button.Enabled = $false
$Backup_DropDown.Enabled = $false}})
#------ TextBoxes Defination ------#
$P_Loc_TextBox.Location = New-Object System.Drawing.point(205,170)
$P_Loc_TextBox.Size = New-Object System.Drawing.Size(300,30)
$Binary_F_loc_TextBox.Location = New-Object System.Drawing.point(205,230)
$Binary_F_loc_TextBox.Size = New-Object System.Drawing.size(300,30)
#------ Buttons Defination -------#
$Fetch_P_loc_Button.Text = "..."
$Fetch_P_loc_Button.Font = $Font
$Fetch_P_loc_Button.size = New-Object System.Drawing.Size(30,20)
$Fetch_P_loc_Button.Location = New-Object System.Drawing.point(505,170)
$Fetch_P_loc_Button.Add_Click({ $P_Loc_TextBox.Text = Get-FolderName })
$Fetch_Binary_F_Button.Text = "..."
$Fetch_Binary_F_Button.Font = $Font
$Fetch_Binary_F_Button.size = New-Object System.Drawing.Size(30,20)
$Fetch_Binary_F_Button.Location = New-Object System.Drawing.point(505,230)
$Fetch_Binary_F_Button.Add_Click({ $Binary_F_loc_TextBox.text = Get-FileName Get-Location})
$Ok_Button.Text = "OK"
$Ok_Button.Font =$Font
#$Ok_Button.DialogResult = [System.Windows.Forms.DialogResult]::OK
$Ok_Button.location = New-Object System.Drawing.point(175,330)
$Ok_Button.Add_Click({
Button-OK
$global:OK_Button_value = "OK"
if($Opt1_Radio_button.checked -eq $true -or $Opt2_Radio_button.Checked -eq $true -and $Backup_DropDown.text.Length -ne 0 -and $P_Loc_TextBox.text.Length -ne 0 -and $Binary_F_loc_TextBox.text.Length -ne 0)
{
$form.Close()
} })
$Cancel_Button.Text = "Cancel"
$Cancel_Button.Font =$Font
$Cancel_Button.location = New-Object System.Drawing.point(325,330)
$Cancel_Button.Add_Click({$form.close(); $global:OK_Button_value = "Cancel"})
# ------ Drop Down Defination ------#
$Backup_DropDown.Location = New-Object System.Drawing.Point(205,110)
$Backup_DropDown.Size = New-Object System.Drawing.Size(100,20)
$Backup_DropDown.DropDownStyle = [System.Windows.Forms.ComboBoxStyle]::DropDownList;
[void] $Backup_DropDown.Items.Add('Full')
[void] $Backup_DropDown.Items.Add('Log')
[void] $Backup_DropDown.Items.Add('Skip Backup')
$form.Maximizebox = $false
$Form.Controls.Add($Exec_type_Label)
$Form.Controls.Add($Backup_label)
$Form.Controls.Add($P_Loc_label)
$Form.Controls.Add($Binary_loc_label)
$Form.Controls.Add($Opt1_Radio_button)
$Form.Controls.Add($Opt2_Radio_button)
$Form.Controls.Add($P_Loc_TextBox)
$Form.Controls.Add($Binary_F_loc_TextBox)
$Form.Controls.Add($Fetch_P_loc_Button)
$Form.Controls.Add($Fetch_Binary_F_Button)
$Form.Controls.Add($Ok_Button)
$Form.Controls.Add($Cancel_Button)
$Form.Controls.Add($Backup_DropDown)
$Form.ShowDialog()
$Form.Dispose()
}
#----------- Function for OK Button ------------#
Function Button-OK
{
if($Opt1_Radio_button.checked -eq $false -and $Opt2_Radio_button.checked -eq $false)
{
[System.Windows.Forms.MessageBox]::Show('Please check one radio button','Patching Tool','OK','Error')
return
} elseif($Opt1_Radio_button.checked)
{
$global:Execution_type = 1
}elseif($Opt2_Radio_button.Checked)
{
$global:Execution_type = 2
}
if($Backup_DropDown.Enabled.Equals($true))
{
if($Backup_DropDown.text.Length -ne 0)
{
$global:Backup_type = $Backup_DropDown.SelectedItem.ToString()
}
else
{
[System.Windows.Forms.MessageBox]::Show('Please select one Backup type','Patching Tool','OK','Error')
return
}
}
if($P_Loc_TextBox.Enabled.Equals($true))
{
if($P_Loc_TextBox.text.Length -ne 0)
{
$global:Pre_req_Folder_Path = $P_Loc_TextBox.Text.ToString()
}
else
{
[System.Windows.Forms.MessageBox]::Show('Please enter Pre-requisites location','Patching Tool','OK','Error')
return
}
}
if($Binary_F_loc_TextBox.text.Length -ne 0)
{
$global:Binary_File_Loc = $Binary_F_loc_TextBox.Text.ToString()
}
else
{
[System.Windows.Forms.MessageBox]::Show('Please enter Binary file location','Patching Tool','OK','Error')
return
}
}
Function Get-FileName($initialDirectory)
{
#[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") |Out-Null
$OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
$OpenFileDialog.initialDirectory = $initialDirectory
$OpenFileDialog.filter = "Executable file (*.exe)| *.exe"
$OpenFileDialog.ShowDialog() | Out-Null
$OpenFileDialog.filename
}
#--- Function to get Change folder location----#
Function Get-FolderName
{
$browse = New-Object System.Windows.Forms.FolderBrowserDialog
$result = $browse.ShowDialog((New-Object System.Windows.Forms.Form -Property @{TopMost = $true }))
if($result -eq 'OK')
{
$browse.SelectedPath
}
else
{
exit
}
}
# ------- Function to get Job Status ---------#
Function Get-JobStatus
{
Param ($JobName)
Start-Sleep -Seconds 2
while(($server.JobServer.Jobs["$JobName"]).CurrentRunStatus -like 'Executing')
{
Start-Sleep -Seconds 10
$server.JobServer.Jobs["$JobName"].Refresh()
}
If(($server.JobServer.Jobs["$JobName"]).CurrentRunStatus -eq 'Idle' -and ($server.JobServer.Jobs["$JobName"]).LastRunOutcome -eq 'Succeeded')
{
Write-Host "Backup job completed successfully"
}
}
# ----------------- Function to perform Prerequisites --------------#
Function Perform-Prerequisites{
try{
#$Folder_Path = Get-FolderName
#----- Creating New File to keep current version of SQL Server ----#
$server.Properties|Where-object {$_.Name -eq 'Product' -or $_.Name -eq 'Edition' -or $_.Name -eq 'VersionString' -or $_.Name -eq 'ResourceVersionString' -or $_.Name -eq 'ProductLevel' } | Format-Table Name, Value | Out-file "$global:Pre_req_Folder_PathVersion_detail.txt"
Write-host "Version details stored in $global:Pre_req_Folder_Path location `n"
#----- Creating New file to keep all SQL services service account detail -----#
Get-WmiObject Win32_Service -Filter "Name like '%SQL%'"|ft __Server,State,Name,DisplayName,StartName, ProcessID, StartMode -AutoSize|Out-File "$global:Pre_req_Folder_PathService_Account_Detail.txt"
Write-host "Service account details stored in $global:Pre_req_Folder_Path location `n"
#------ Stopping SQL Services --------#
$Master_DB_loc = $server.Information.MasterDBPath
$Resource_DB_Loc = $server.Information.RootDirectory
Write-host "Stopping MSSQL services.... `n"
Get-Service "*SQL*" | Stop-Service -Force
#----- Copying all system DB files to New Folder ------#
Start-Sleep -Seconds 2
Get-ChildItem -Path "$Master_DB_loc" -Filter 'mast*' | Copy-Item -Destination "$global:Pre_req_Folder_Path"
Get-ChildItem -Path "$Master_DB_loc" -Filter 'model*'| Copy-Item -Destination "$global:Pre_req_Folder_Path"
Get-ChildItem -Path "$Master_DB_loc" -Filter 'MSDB*' | Copy-Item -Destination "$global:Pre_req_Folder_Path"
Get-ChildItem -Path "$Resource_DB_LocBinn" -Filter '*systemresource*' | Copy-Item -Destination "$global:Pre_req_Folder_Path"
Write-host "All system databases files copied to $global:Pre_req_Folder_Path location `n"
}
catch{
Write-Host $_.Exception.Message
Write-Host "Prerequisites are failed"
exit
}
}
# --------------- Function to Execute backup job -----------------#
Function Execute-Backupjob{
Try{
switch($global:Backup_type)
{
'Full'
{
$JobName = ($server.JobServer.Jobs| Where-Object {$_.Name -like 'Give JOb Name'}).Name #Provide Job name here if using SQL Agent jobs to perform backups
Write-Host "Executing $JobName Job"
Invoke-Sqlcmd -ServerInstance localhost -Query ("EXECUTE msdb.dbo.sp_start_job '$JobName'")
Get-JobStatus $JobName
break;
}
'Log'
{
$JobName = ($server.JobServer.Jobs| Where-Object {$_.Name -like 'Job Name'}).Name #Provide Job name here if using SQL Agent jobs to perform backups
Write-Host "Executing $JobName Job"
Invoke-Sqlcmd -ServerInstance localhost -Query ("EXECUTE msdb.dbo.sp_start_job '$JobName'") |Out-Null
Get-JobStatus $JobName
break;
}
'Skip Backup'
{
Write-Host "Backup has been skipped `n"
break;
}
}
}
catch{
Write-Host $_.Exception.Message
Write-Host "Backup job execution was failed"
exit
}
}
Function Get-Logfile{
#$version = $server.Information.Version.Major
$version = $version.ToString() + '0'
$progfiles = ${env:ProgramFiles}
$Path = $progfiles + "Microsoft SQL Server$versionSetup BootstrapLog"
Write-host "Use following path to access the log file"
write-host "$PathSummary.txt" #to_verify_the_path
cd $Path
.Summary.txt
}
Function Install-Patch{
try{
#------ Installing SQL server patch -------#
Write-host "Patch installation started..."
powershell.exe $global:Binary_File_Loc /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances
$installation_process = Get-Process "*SQLServer*"
while($installation_process.Count -eq 0)
{
Write-host "Patch File is getting extracted ...."
Start-Sleep -Seconds 10 # --- Time given to extract the patch file ---- #
$installation_process = Get-Process "*SQLServer*"
}
#---------- TO hold the powershell session ---------#
$installation_process = Get-Process "*SQLServer*"
while($installation_process.Count -ne 0)
{
Write-host "Installation is in progess ...."
Start-Sleep -Seconds 30
$installation_process = Get-Process "*SQLServer*"
}
}
catch{
Write-Host $_.Exception.Message
Write-Host "Patch installation was failed"
exit
}
}
Get-Form
# write-host $global:OK_Button_value
# write-host $global:Backup_type
# Write-Host $global:Execution_type
# write-host $global:Binary_File_Loc
# write-host $global:Pre_req_Folder_Path
#-------------------- Main code ----------------------------#
if($global:OK_Button_value -eq 'OK')
{
if($global:Execution_type -eq 1)
{
Execute-Backupjob
Perform-Prerequisites
Install-Patch
Get-Logfile
}
elseif($global:Execution_type -eq 2)
{
Write-host "Stopping MSSQL services.... `n"
Get-Service "*SQL*" | Stop-Service -Force
Install-Patch
Get-Logfile
}
}
else
{
exit
} 
