nanoDBA

#requires -version 5.0
#Requires -RunAsAdministrator
<# DANGER REBOOTING!
   This is meant for a local machine in a scheduled task 
   and is not handling remoting or remote credentials
#>
[CmdletBinding()]
param (
    [Parameter()][int]$DelayMinutes = 15 #default to 15 minute delay if this parameter is not supplied
)

nanoDBA

$computerName = "some.computer.name"
$portNumber = 3389
while ((Test-NetConnection -ComputerName $computerName -Port $portNumber).TcpTestSucceeded -EQ $false){
    Write-Host "Checking TCP port $portNumber on $computerName..."
    Start-Sleep -Seconds 5
    Get-Date -Format "yyyy-MM-ddTHH:mm:ss (K)"

    if ((Test-NetConnection -ComputerName $computerName -Port $portNumber).TcpTestSucceeded -EQ $true){
         Write-Host -ForegroundColor Magenta "Yay!!  TCP port $portNumber on $computerName is back online!"
         while($true){(New-Object System.Media.SoundPlayer $((Get-ChildItem -Path "$env:windir\Media\tada.wav").FullName)).Play(); Start-Sleep -Seconds 2}

nanoDBA

<# download and install Solarwinds SentryOne Plan Explorer #>
$filenamePlanExplorerInstall = "$env:USERPROFILE\downloads\" + ([string](Get-Date -format "yyyy-MM-dd")) + "_SolarWinds-PlanExplorer.exe"
Start-BitsTransfer -Source 'https://downloads.solarwinds.com/solarwinds/Release/FreeTool/SolarWinds-PlanExplorer.exe' -Destination $filenamePlanExplorerInstall
& $filenamePlanExplorerInstall /install /passive /norestart

nanoDBA

if (! ( (Get-WindowsFeature -Name Windows-Defender-Features).Installed)) {
    Write-Output "Windows Defender is not installed" | Out-String | Write-Host -ForegroundColor Yellow
}
if ( (Get-WindowsFeature -Name Windows-Defender-Features).Installed) { 
    #Disable Windows Defender http://www.thomasmaurer.ch/2016/07/how-to-disable-and-configure-windows-defender-on-windows-server-2016-using-powershell/
    Set-MpPreference -DisableRealtimeMonitoring $true
    # uninstall Windows Defender https://technet.microsoft.com/en-us/windows-server-docs/security/windows-defender/windows-defender-overview-windows-server
    Get-WindowsFeature -Name Windows-Defender-Features
    Uninstall-WindowsFeature -Name Windows-Defender-Features -Confirm:$false
    Get-WindowsFeature -Name Windows-Defender-Features

nanoDBA

<# DANGER REBOOTING! #>
$msg = "Due to installs/updates of SQL Prompt, this computer must be restarted. You have 15 minutes to save your work"
$delay = 900 # seconds
shutdown /r /f /d P:4:1 /t`$($delay) /c "$msg" 2>$null
if ($LastExitCode -ne 0) {
    Write-Host "Cannot reboot $PC ($LastExitCode)" -ForegroundColor black -BackgroundColor red

}
else {
    #LogWrite "$env:username,$PC,Reboot Sent,$datetime" #fix this...

nanoDBA

<# How to use TSS Tools with Microsoft Support #>
<# Pretty Timestamp prompt using dbatools module#>function Prompt{Write-Host "[" -NoNewline; Write-Host (Get-Date -Format "HH:mm:ss") -ForegroundColor Gray -NoNewline;try{$history = Get-History -ErrorAction Ignore;if ($history) {Write-Host "][" -NoNewline;if (([System.Management.Automation.PSTypeName]'Sqlcollaborative.Dbatools.Utility.DbaTimeSpanPretty').Type){Write-Host ([Sqlcollaborative.Dbatools.Utility.DbaTimeSpanPretty]($history[-1].EndExecutionTime - $history[-1].StartExecutionTime)) -ForegroundColor Gray -NoNewline} else{Write-Host ($history[-1].EndExecutionTime - $history[-1].StartExecutionTime) -ForegroundColor Gray -NoNewline;}}}catch { }Write-Host "] $($executionContext.SessionState.Path.CurrentLocation.ProviderPath)" -NoNewline;"> "}
cd $env:temp
$targetTssDir = 'c:\tss_tools'
if (!(Test-Path $targetTssDir)) {mkdir $targetTssDir };

Start-BitsTransfer -Destination .\tss_tools.zip  -Source 'http://aka.ms/getTSS'
Expand-Archive .\tss_tools.zip -Outpu

nanoDBA

<# download and install latest SSMS release #>
$SSMS_filename = "$env:USERPROFILE\downloads\SSMS-Setup-ENU-" + ([string](Get-Date -format "yyyy-MM-dd")) + ".exe" 
Start-BitsTransfer -Source 'https://aka.ms/ssmsfullsetup' -Destination $SSMS_filename
& $SSMS_filename /install /passive
<# Thanks to @sqltoolsguy and his team for creating the aka.ms links! 
https://twitter.com/sqltoolsguy/status/1011754064516804608 #>

nanoDBA

<# clone latest master #>
$gitHubLink = "https://github.com/powerline/fonts.git"
$gitHubFolderName = (split-path $gitHubLink -Leaf).Replace('.git','')
$targetFolder = "$env:USERPROFILE\downloads\Repos\$gitHubFolderName"
<# *** This path and all subdirs are about to be deleted - Are you sure? ***  #>
If(Test-Path $targetFolder) {Remove-Item -Recurse -Force $targetFolder\* -Confirm}
If(!(Test-Path $targetFolder)) {mkdir $targetFolder }
git clone -b master $gitHubLink $targetFolder
Invoke-Item $targetFolder

nanoDBA

# Run a query against multiple databases 
# by querying groups of instances 
# listed in the CMS in parallel,
# then output a spreadsheet
#
# Modules used: PoshRSJob, dbatools, ImportExcel
#
# Stop & remove all PoshRSJobs - Are you sure you want to do this?
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob

nanoDBA

# Find all the SQL Agent Jobs by querying groups 
# of instances listed in the CMS
# in parallel, then output a spreadsheet
#
# Modules used: PoshRSJob, dbatools, ImportExcel
#
# Stop & remove all PoshRSJobs - Are you sure you want to do this?
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob

# Use CMS server and optional group name to identify SQL instance names, remove duplicates