Skip to content

Instantly share code, notes, and snippets.

Aaron Nelson SQLvariant

Block or report user

Report or block SQLvariant

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
@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 / 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 / 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 / Mmmm_Chocolatey.ps1
Last active Sep 4, 2019
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 / 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 / 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 / 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 / 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 / AdventureWorksDW2017_ExportExcel.ps1
Last active Mar 14, 2019
Export every table in the AdventureWorksDW2017 db to Excel
View AdventureWorksDW2017_ExportExcel.ps1
#Requires -Modules SqlServer
#Requires -Modules ImportExcel
<# The AdventureWorksDW2017 only has 29 tables and they're all under 1 million rows.#>
cd SQLSERVER:\SQL\LocalHost\SQL2017\Databases\AdventureWorksDW2017\Tables
<# Scenario #1 A) all Dimensions in a single file,
and B) each Fact table in their own file. #>
<# A) Every Dimension table in a worksheet named after the table, the same Excel file #>
dir | WHERE { $_.name -like 'dim*' } |
@SQLvariant
SQLvariant / QuickSQLContainer_wAW2016.ps1
Created Mar 12, 2019
Create a SQL-on-Linux container, and restore the AdventureWorks2016 DB
View QuickSQLContainer_wAW2016.ps1
$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak"
$BakFile = "$($Home)\Downloads\AdventureWorks2016.bak"
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile
mkdir C:\SQLData\Docker\SQLDev02
#dir C:\SQLData\Docker\SQLDev02 | OGV -PassThru | Remove-Item
Copy-Item -Path "$($Home)\Downloads\AdventureWorks2016.bak" -Destination C:\SQLData\Docker\SQLDev02
<# Create a Container with a volume mounted from the host #>
You can’t perform that action at this time.