Skip to content

Instantly share code, notes, and snippets.

Avatar

Aaron Nelson SQLvariant

View GitHub Profile
View GetSQLBuffers_wProvider_PipeTo_Out-SquarifiedTreeMap.ps1
<# This is the code used to tell you
which databases are using up the RAM on
your SQL Server is great information to know
Queries are from https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/ #>
$SQLInstance = 'localhost\SQL2016'
Invoke-Sqlcmd -ServerInstance $SQLInstance -Database master -Query "
DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value
View GetLastNightsFailedSQLAgentJobs.ps1
<#1#>
Get-Command -Module SqlServer -Noun SqlAgent*
<#2#>
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed
<#3#>
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed |
Out-GridView
View Install_SQLServerPerformanceDashboardReporting.ps1
<# This script is intented to help automate Steps 2-7 for downloading & installing the SQL Server Performance Dashboard Reports
https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/ #>
<# If the ReportingServicesTools is not present, download the ReportingServicesTools module from GitHib #>
try {Import-Module ReportingServicesTools -ErrorAction Stop} catch {Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools)} finally {Import-Module ReportingServicesTools}
<# Setting our GitHub resources to variables #>
$ZipURL = "https://github.com/Microsoft/tigertoolbox/raw/master/SQL-performance-dashboard-reports/SQL%20Server%20Performance%20Dashboard%20Reporting%20Solution.zip"
$SQLURL = 'https://github.com/Microsoft/tigertoolbox/raw/master/SQL-performance-dashboard-reports/setup.sql'
@SQLvariant
SQLvariant / 000_Downlading_SqlServer_module.ps1
Last active May 22, 2017
SQL PowerShell Demos for Spring of 2017
View 000_Downlading_SqlServer_module.ps1
Install-Module SqlServer
<# If you don't have admin #>
Install-Module SqlServer -Scope CurrentUser
<# Find the commands #>
Get-Command -Module SqlServer -CommandType Cmdlet |
Out-GridView
<# What if you need to install on a server behind a firewall #>
View 000_Setup
<#
Let's go shopping in the PowerShell Store
Ok, it is not actually called the 'store'
it is called the PowerShell Gallery
#>
Start-Process http://PowerShellGallery.com
<# Use the Search #>
<#
View Top15Palatte
{
"name": "Top15Palatte",
"dataColors": ["#887e6f","#295270","#e6942d","#d52c33","#9f9311","#9fc2dc","#ffc945","#665d3c","#54758D","#DD565C","#EBA957","#9f9f9f","#276B75","#b6749c","#9d9898"
],
"background":"#ffffff",
"foreground": "#003052",
"tableAccent": "#9fc2dc"
View Get-DisksSpace.PS1
Function Get-DisksSpace
{
<#
.SYNOPSIS
Grabs Hard Drive & Mount Point space information.
.DESCRIPTION
Grabs Hard Drive & Mount Point space information.
.PARAMETER serverName
Accepte 1 or more servernames, up to 50 at once.
@SQLvariant
SQLvariant / ExcelSchemaAndData_to_PowerBI_DataSet.ps1
Created Mar 11, 2019
Use PowerShell to extract Excel Worksheey Schema and Data to create Power BI DataSet
View ExcelSchemaAndData_to_PowerBI_DataSet.ps1
<# Literally one of the oldest SQL PowerShell examples, and it now works on PSCore.
I'm only doing this step to generate data to place in an Excel spreadsheet.
IMPORTANT: If you already have an Excel spreadsheet, skip this section! #>
ls 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse |
WHERE {$_.Mode -ne 'd' } |
foreach {
Invoke-Sqlcmd -ServerInstance $_.Name -Database master -OutputAs DataTables -Query "
SELECT @@ServerName AS 'ServerName',
DB_NAME(dbid) AS 'DatabaseName',
name AS 'LogicalName',
@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 #>
@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,
You can’t perform that action at this time.