Skip to content

Instantly share code, notes, and snippets.

Avatar

Aaron Nelson SQLvariant

View GitHub Profile
@SQLvariant
SQLvariant / ADS_Extension.ps1
Created Mar 14, 2019
Common SQL PowerShell examples for the SqlServer module
View ADS_Extension.ps1
<# Get the Server SMO objects for ServerA & ServerB
The default output for this command will include the Instance name, version, Service Pack, & CU Update Level #>
Get-SqlInstance -ServerInstance ServerA, ServerB
<# Sample Output #>
Instance Name Version ProductLevel UpdateLevel
------------- ------- ------------ -----------
ServerA 13.0.5233 SP2 CU4
ServerB 14.0.3045 RTM CU12
<# Get the Instance name, version, Service Pack, & CU Update Level,
@SQLvariant
SQLvariant / Get-History2.ps1
Created Mar 19, 2019
Simple function to give you the duration of all command history in PowerShell
View Get-History2.ps1
function Get-History2
{
Get-History |
SELECT Id, @{Label="TotalRunningTime";Expression={$_.EndExecutionTime - $_.StartExecutionTime}}, CommandLine, ExecutionStatus, StartExecutionTime, EndExecutionTime
}
@SQLvariant
SQLvariant / Get-MachineInfo.ps1
Created Mar 19, 2019
Simple PowerShell function to retrieve OS info from a Windows machine.
View Get-MachineInfo.ps1
function Get-MachineInfo($ServerName="localhost")
{
Get-WmiObject win32_computersystem -ComputerName $ServerName |
SELECT DNSHostName,
Manufacturer,
Model,
SystemType,
@{Name="TotalPhysicalMemoryInMB";Expression={"{0:n2}" -f($_.TotalPhysicalMemory/1mb)}},
NumberOfLogicalProcessors,
NumberOfProcessors,
@SQLvariant
SQLvariant / Export-CSV-Append.ps1
Created Mar 19, 2019
PowerShell proxy cmdlet – function which wraps standard Export-CSV cmdlet but adds handling of the -Append parameter
View Export-CSV-Append.ps1
#Requires -Version 2.0
<#
This Export-CSV behaves exactly like native Export-CSV
However it has one optional switch -Append
Which lets you append new data to existing CSV file: e.g.
Get-Process | Select ProcessName, CPU | Export-CSV processes.csv -Append
For details, see
http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/
@SQLvariant
SQLvariant / Mmmm_Chocolatey.ps1
Last active Feb 24, 2021
Install SQL / Data Developer Desktop Tools from Chocolatey
View Mmmm_Chocolatey.ps1
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
choco install chocolatey -y
choco install sql-server-management-studio -y
choco install azure-data-studio -y
choco install azuredatastudio-powershell -y
choco install git.install -y
choco install vscode -y
choco install vscode-powershell -y
choco install powerbi -y
@SQLvariant
SQLvariant / Find-MostRecentFullBackup.ps1
Created Mar 28, 2019
Find the file-name of the most recent Full Database Backup for a SQL Server database
View Find-MostRecentFullBackup.ps1
function Find-MostRecentFullBackup([string]$ServerInstance, $database="master"){
#$db = "Adventureworks"
$rs = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database msdb -Query "
WITH LatestBackupSet (database_name, BackupsAgo, backup_start_date, media_set_id)
AS
(
SELECT database_name
, ROW_NUMBER() OVER (PARTITION BY database_name ORDER BY backup_start_date DESC) AS 'BackupsAgo'
, backup_start_date
@SQLvariant
SQLvariant / DockerDesktop-with-SQL-PowerShell-1.ps1
Created Apr 1, 2019
Create a SQL-on-Linux Docker Container and Restore the AdventureWorks2016 Database
View DockerDesktop-with-SQL-PowerShell-1.ps1
<# 0A) Before any of this can work, you must have Docker Destop running.
You must also have the latest SqlServer module installed from the PowerShell Gallery.#>
Install-Module SqlServer
<# 0B) Use this code to download the AdventureWorks2016.bak file from GitHub: #>
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak";
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak";
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;
<# 1) Create a SQL-on-Linux Docker Container with just the code below.
@SQLvariant
SQLvariant / DockerDesktop-with-SQL-PowerShell-2.ps1
Created Apr 9, 2019
Create a SQL-on-Linux Docker Container with a Host Mount and Restore the 5 AdventureWorks Databases
View DockerDesktop-with-SQL-PowerShell-2.ps1
<# 0A) Before any of this can work, you must have Docker Destop running.
You must also have the latest SqlServer module installed from the PowerShell Gallery.#>
Install-Module SqlServer
<# 0B) Use this code to download the AdventureWorks2016.bak file from GitHub: #>
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak";
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak";
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;
<# 1) Create a SQL-on-Linux Docker Container with just the code below.
@SQLvariant
SQLvariant / Start-Pomodoro.ps1
Last active Apr 29, 2019
A PowerShell function to create Pomodoro timer. Defaults to 25 minute timer.
View Start-Pomodoro.ps1
<# PLEASE NOTE: I am not the original author of this function.
I found it online years ago, and have been using it ever since.
If you are the original author, please ping me and let me know,
so I can give you proper credit.
Based on another function in the PowerShell Gallery, the orginial author might be Nathan.Run() http://nathanhoneycutt.net/blog/a-pomodoro-timer-in-powershell/
#>
Function Start-Pomodoro
{
Param (
@SQLvariant
SQLvariant / BuildContainer_FromImage.ps1
Last active Apr 30, 2019
simple dockerfile for building a SQL-on-Linux container. Place this file in your c:\temp directory
View BuildContainer_FromImage.ps1
<# Step 0)
Put the dockerfile & AdventureWorks2016.bak into your c:\temp
#>
$dockerfileURL = "https://gist.githubusercontent.com/SQLvariant/ebe7fa49216badb6b53339818ca1eda9/raw/ded3e7f988309d311b6f389257e499cb66d5dd39/dockerfile";
$dockerfile = c:\temp\BuildContainer_FromImage.ps1;
Invoke-WebRequest -Uri $dockerfileURL -OutFile $dockerfile;
Copy-Item -Path "$($Home)\Downloads\AdventureWorks2016.bak" -Destination C:\temp
<# First, build the image #>