# See if we need to remove brackets remove square brackets. Rename files.
#
$RenamePath = '\\Sqlprod7b\e$\MSSQL\SCRIPTS\master\StoredProcedureTFS'
get-childitem $RenamePath | ForEach-Object { Move-Item -LiteralPath $_.Fullname $_.Fullname.Replace("[","").Replace("]","") -ErrorAction Stop }
SQL SMO Scripting Options broken in versions 2012-2016
Some notes about SSMS.
Here’s where the problem lies. If you are installing SQL 2012-2016 your Scripting Options may not work.
For SQL Server 2014 SSMS is included in the SQL install and SP.
The fix:
Even though you might only be installing SQL Server 2014 because SMO Options didn’t always work between versions 2012-2016 we need to install SQL Server 2017 SSMS 17.9.1.
e.g. What was broken was the SMO Scripting Option for
$Scripter.Options.NoFileGroup = $false
which is the default but even explicitly forcing it had no effect.
Simply adding SSMS 17.9.1 allowed correct scripting from the server.
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
SSMS 17.9.1 is the current General Availability (GA) version of SSMS
1) download Download SQL Server Management Studio 17.9.1
2) download Download SQL Server Management Studio 17.9.1 Upgrade Package (upgrades 17.x to 17.9.1)
Version Information
Release number: 17.9.1
Build number: 14.0.17289.0
Release date: November 21, 2018
PowerShell: Get .NET PS and OS versions on all computers
Run this from an account which has access to all servers in the list.
# Get .NET Version and PowerShell Versions for all computers in AllComputersList.
#
$remoteCommand = @"
Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -recurse |
Get-ItemProperty -name Version,Release -EA 0 |
Where { `$_.PSChildName -match '^(?!S)\p{L}'} | sort Version -Descending |
Select-Object -first 1 -ExpandProperty Version
"@
$VersionReport = @()
$AllComputersList |%{
$CurrentServer = $_
$dotNetVersion = $(Invoke-Command -Computername $CurrentServer -Scriptblock $ScriptBlock -ErrorAction SilentlyContinue)
$PsVersion = $(Invoke-Command -Computername $CurrentServer -Scriptblock {$PSVersionTable.psversion} -ErrorAction SilentlyContinue)
$os = Get-WmiObject -class Win32_OperatingSystem -computername $CurrentServer
$Versions = [pscustomobject]@{
Computer = $CurrentServer
NETVersion = $dotNetVersion
PSVersion = $PsVersion
OSVersion = $os.Version + "`t" + $os.Caption
}
$VersionReport += $Versions
Write-Host "$(Get-Date -Format g) [$CurrentServer] `t.NET Version $dotNetVersion `tPSVersion $PsVersion `t$($os.Version)"
}
$VersionReport | sort PSVersion,Computer |Out-GridView
Powershell: Get all installed applications
Produce a nice output of our best guess at all the installed applications on a server or PC.
$ComputerName = 'SQLPROD'
# Get Software list for a 64-bit computer SOFTWARE
$remoteCommand = @"
Get-ItemProperty HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\*
"@
$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 1
$Software1 = Invoke-Command -Computername $ComputerName -Scriptblock $ScriptBlock
# Get Software list for a computer Wow6432Node
$remoteCommand = @"
Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\*
"@
$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 2
$Software2 = Invoke-Command -Computername $ComputerName -Scriptblock $ScriptBlock
$ComputerSoftwareList = [Pscustomobject]@()
if ($Software1) {
$ComputerSoftwareList += $Software1 | Sort-Object DisplayName
$ComputerSoftwareList.Count
}
if ($Software2) {
$ComputerSoftwareList += $Software2 | Sort-Object DisplayName
$ComputerSoftwareList.Count
}
$ComputerSoftwareList.Count
$OutGrid = $ComputerSoftwareList |
Sort-Object DisplayName |
Select-Object DisplayName, DisplayVersion, Publisher, InstallDate -Unique
$Global:seq = 1
$OutGrid |
Select-Object @{n=“Seq”; e={$Global:seq; $Global:seq++;}},
@{n="Computer";e={"$ComputerName"}}, DisplayName, DisplayVersion, Publisher, InstallDate |
Out-GridView
PowerShell: What jobs are currently executing, running now?
# ------------------------------------------------------------------------------------------------------------------------
# What jobs are currently executing, running now? RUNNING JOBS
$ExclusionList = @('SQLEXT1')# @() # # @() # @() #
#
$RunningJobs = Get-SQLExecutingJobs -AllServersList $AllServersList -ExclusionList $ExclusionList -ErrorAction Continue # -Verbose -WarningAction SilentlyContinue
# Summarize
$RunningJobs |Sort-Object ServerName |
Select-Object RunDateTime, @{n="Duration ";e={ ("{0:dd\:hh\:mm\:ss}" -f (NEW-TIMESPAN –Start $_.RunDateTime –End (GET-DATE))) }},
ServerName, JobName,current_execution_step,last_run_date,last_run_time |
Format-Table -autosize
PowerShell: Get the first files in each folder for each day
$SetFileNames = @()
$TESTFiles |
Sort-Object Directory, LastWriteTime, Name |
Group-Object -Property {Get-Date $_.LastWriteTime -Format d } |
ForEach-Object {
$_.Group |
ForEach-Object -Begin {
$counter = 0
$FirstFile = $_
# Grab the files for each folder which are the first file of each day.
$SetFileNames += $FirstFile.Group[0]
} -Process {
$counter++;
# Do work here
}
}
$SetFileNames
PowerShell: Get the most recent file in a path
This answers what was the most recent change in a folder and subfolder.
gci $Path -Recurse |?{-not $_.PsIsContainer} | sort LastWriteTime | select -last 1
PowerShell: Sum of selected file sizes
$FilesToDelete = Get-ChildItem -Path "$TopPath\*" -Include *.safe, *.bak, *.trn, *.log -Recurse ($FilesToDelete |Measure-Object -property length -sum).Sum / 1MB
PowerShell: Get list of registered SQL Servers
No reason to maintain more than one list of SQL Servers when you can query SSMS’s Registered Servers.
Import-Module SQLPS -DisableNameChecking
$SQLInstanceProd = @()
$ServerGroup = 'PRODUCTION'
$SQLPath = "SQLSERVER:\sqlregistration\Database Engine Server Group\PROD*\"
$SQLInstanceProd += @(get-childitem $SQLPath -Recurse).Name | ?{$_ -notmatch '\d\d\d\d'} | % {
$MyPSObject = New-Object PSObject -Property @{
SQLInstance = $_
ServerGroup = $ServerGroup;
}
$MyPSObject
}
$SQLInstanceProd
Putting that logic into Get-RegisteredServers I populate the following list in my Profile.
$AllServersList = (Get-RegisteredServers).SQLInstance
$AllComputersList = $AllServersList | %{ $_.split('\')[0] }
$SQLInstanceDev = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'DEV'}).SQLInstance
$SQLInstanceTest = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'TEST'}).SQLInstance
$SQLInstanceProd = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'PROD'}).SQLInstance
PowerShell: Get Windows Update scheduled date
Look for WU install dates for all servers.
$DayOfWeek = @{n='DayOfWeek';e={ ($_.LastRunTime).DayOfWeek }}
$Computer = @{n='Computer';e={$ComputerName}}
$TimeFromNow = @{n='HoursFromNow';e={"{0}" -f (New-TimeSpan -Start (Get-date) -End $_.NextRunTime) } }
$SQLInstanceDev + $SQLInstanceTEST + $SQLInstancePROD | %{
$ComputerName = $_.Split('\')[0]
Write-Host $ComputerName
# Actually get STATE for Windows Updates for each computer
.\adhoc\Get-ScheduledTask.ps1 -ComputerName $ComputerName |
Where-Object {$_.Name -in 'ProgramDataUpdater','AUScheduledInstall' } |
Select-Object $Computer, $TimeFromNow, Name, State, Enabled, LastRunTime, NextRunTime, ComputerName, $DayOfWeek
} | Out-GridView