Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
SQL PowerShell Demos for Spring of 2017
Install-Module SqlServer
<# If you don't have admin #>
Install-Module SqlServer -Scope CurrentUser
<# Find the commands #>
Get-Command -Module SqlServer -CommandType Cmdlet |
<# What if you need to install on a server behind a firewall #>
Save-Module SqlServer -Path c:\temp\
<# Just in case you run into an issue with the old SQLPS module #>
Get-Module -ListAvailable -Name SQL*
<# Where are my backups? #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\DEFAULT).BackupDirectory
<# Backup all my databases now please #>
Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } |
Backup-SqlDatabase -CompressionOption On;
<# Next, a message from Grant #>
<# Go to the data file directory #>
<# Grant said it, so we better do it. #>
#Install-Module dbatools
Test-DbaLastBackup -SqlServer localhost -Destination $new | Out-GridView
This script is intented to help automate Steps 2-7 for downloading & installing the SQL Server Performance Dashboard Reports #>
<# If the ReportingServicesTools is not present, download the ReportingServicesTools module from GitHib #>
try {Import-Module ReportingServicesTools -ErrorAction Stop} catch {Install-module -Name ReportingServicesTools -Scope CurrentUser} finally {Import-Module ReportingServicesTools}
<# Setting our GitHub resources to variables #>
$ZipURL = ""
$SQLURL = ''
<# Where to place the files when they are downloaded
$ZipFile will go to the current users 'Downloads' folder.
$ReportsBaseFolder is where the SSRS Solution will be unzipped to.
You could change this to somewhere else like "$($env:USERPROFILE)\Documents\Visual Studio 2015\Projects"
$SQLFile is the Setup.SQL that must be run on each SQL Server before these reports can work. #>
$ZipFile = "$($env:USERPROFILE)\Downloads\"
$ReportsBaseFolder = 'C:\SQL Server Performance Dashboard'
$SQLFile = "$($ReportsBaseFolder)\Setup.SQL"
<# SSRS Instance, this is where the reports will be rendered from.
You probably need to change this to something like 'http://MyReportServer/ReportServer'. If you have a named SSRS instance 'http://MyReportServer/ReportServer_SQL2016' #>
$SSRSInstance = 'http://localhost/ReportServer_SQL2016'
$NewSSRSReportFolder = 'SQL Server Performance Dashboard'
<# Start up a web client and download the GitHub resources #>
$webclient = New-Object
<# UnZip the Reporting Solution Zip file #>
Expand-Archive $ZipFile -DestinationPath $ReportsBaseFolder -Verbose -Force
<# Now that the reports are unzipped, download the SQL file to that same directory #>
Write-Host -ForegroundColor White -Separator "|" -BackgroundColor DarkBlue -Object "Finished Downloading & UnZipping Files"
<# Deploy the dashboard reports to the $NewSSRSReportFolder ('SQL Server Performance Dashboard') folder of an SSRS instance
Note: We are creating the folder using New-RsFolder, you may need to skip this step if you’ve already run it once. #>
New-RsFolder -ReportServerUri $SSRSInstance -Path / -Name $NewSSRSReportFolder -Verbose
Write-RsFolderContent -ReportServerUri $SSRSInstance -Path "$($ReportsBaseFolder)\SQL Server Performance Dashboard\" -Destination /$NewSSRSReportFolder -Verbose
Get-RsFolderContent -ReportServerUri $SSRSInstance -Path /$NewSSRSReportFolder |
SELECT Name, Path, TypeName, Size |
Format-Table -AutoSize
<# Loop through Registered Servers & deploy the Setup.SQL file to each instance
You can also use a Central Management Server to list your SQL servers by swapping 'Database Engine Server Group' for 'Central Management Server Group' #>
#foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Host\ | where {$_.Mode -ne 'd'} )
#Invoke-Sqlcmd -ServerInstance $RegisteredSQLs.Name -Database msdb -InputFile $SQLFile
<# Go to SSRS and make sure everythng works
You URL should look something like http://localhost/Reports_SQL2016/report/SQL%20Server%20Performance%20Dashboard/performance_dashboard_main #>
Start-Process "$($SSRSInstance -replace 'Server', 's')/report/$($NewSSRSReportFolder)/performance_dashboard_main"
<# Reset Demo
Remove-Item $ZipFile;
Remove-Item $ReportsBaseFolder -Recurse;
Remove-RsCatalogItem -ReportServerUri $SSRSInstance -Path "/$($NewSSRSReportFolder)"
#Modules on this machine
Get-Module -ListAvailable
Remove-Module SqlServer;
#Load the SqlServer Module
Import-Module SQLServer
#New Resource
<# What's inside SQLRegistration? #>
CD 'Database Engine Server Group'
CD Host
<# What's inside that "SQL" folder? #>
<# What's inside that computer? #>
CD 'localhost'
<# Let's grab one of those instances and have a look around. #>
<# We can take these results and send them straight into a variable.
When we do that, PowerShell takes care of checking to see if a variable by
that name exists already, and assigns a data type to it based on what is being piped in.
Note 1: You can assign your own data type if you wish. #>
$MySQL2016Instance = GET-ITEM 'SQL2016'
<# Let's take a quick look at all of the databases #>
CD SQLSERVER:\SQL\localhost\SQL2016\Databases\
<# We're going to take a quick detour real quick.
PowerShell completes the whole line before placing the values inside
the variable. This could affect the data type assigned. #>
$bob = Get-ITEM 'AirStats'
$ralph = Get-ITEM 'AirStats' | SELECT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable
<# Let's take a quick look at all of my tables #>
CD SQLSERVER:\SQL\localhost\SQL2016\databases\AirStats\tables\
DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
Select Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
ft -AutoSize
<# Let's take a quick look at all of my tables #>
DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
Select Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
<# Let's take a quick look at tables with indexes larger than data #>
DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
WHERE {$_.IndexSpaceUsed -gt $_.DataSpaceUsed} |
SELECT Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
<# Traversing the SQL Server Provider is just like Object Explorer #>
cd SQLServer:\sql\localhost\default\Databases\AdventureWorks2014\Tables\
#What can we do whith that? #Never Type in a Demo!
#Now this doesn't have to me local. We're basically navigating Object Explorer
#Let's script this table out
$PTH = get-item Production.TransactionHistory
#How did I know that I can script it out?
#Switch to Side-by-Side
get-item Production.TransactionHistory | get-member
get-item Production.TransactionHistory | get-member -MemberType Methods
#What is this object's type?
Get-Command -Module SQLas*
CD SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Dimensions\
<# Hopefully you don't need the below command any more #>
#dir | SELECT Name, State, LastProcessed, ProcessingMode, CurrentStorageMode | Out-GridView -PassThru;
dir | %{$_.Process()}
<# Process Dimensions #>
dir |
Out-GridView -PassThru | %{
Invoke-ProcessDimension -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
Get-Command -Module SqlServer -Name Invoke-Process*
<# Process Cubes #>
Dir 'SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Cubes\Adventure Works\MeasureGroups'|
Dir 'SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\Cubes\'|
Out-GridView -PassThru | %{
Invoke-ProcessCube -Name $_.Name -Database $_.ParentDatabase -ProcessType ProcessFull
<# Can Also work with Tabular Databases #>
Dir SQLSERVER:\SQLAS\LOCALHOST\TAB2016\Databases\HealthCheck\Cubes\Model\MeasureGroups\
Dir SQLSERVER:\SQLAS\LOCALHOST\TAB2016\Databases\'Adventure Works Internet Sales'\Tables\'Internet Sales'\Measures\ |
<# I have a New SQL 2016 instance
I want to make it look just like my SQL 2014 instance #>
$serverDefault = Connect-DbaSqlServer -SqlServer localhost
$Server = Connect-DbaSqlServer -SqlServer localhost\SQL2016
<# SMO is Hard. You have to use dots and stuff. Also, I can't read without a table! | Format-Table -AutoSize #>
$serverDefault.Configuration.Properties | Format-Table -AutoSize
$DefaultSQL = $serverDefault.Configuration.Properties |
Select-Object DisplayName, RunValue
$SQL2016 = $Server.Configuration.Properties |
Select-Object DisplayName, RunValue
<# Ok, let's just compare #>
Compare-Object -ReferenceObject $SQL2016 -DifferenceObject $DefaultSQL -Property DisplayName, RunValue |
<# Build it your own or use awesome code like this: #>
<# Just here to reset the demo #>
$DefaultSQL = $Null
$SQL2016 = $Null
Get-SqlAgent -ServerInstance 'localhost' |
Get-SqlAgentJob | Out-GridView
<# Get-SqlAgentJob > Show-Command #>
Get-SqlAgentJob -ServerInstance localhost\SQL2016
Get-SqlAgentJob -ServerInstance localhost, localhost\SQL2016
<# Compare this to OED of SQL Agent #>
Get-SqlAgentJob -ServerInstance localhost |
Get-SqlAgentJob -ServerInstance localhost |
Get-SqlAgentJob -ServerInstance localhost |
Where { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }
Get-SqlAgentJob -ServerInstance localhost |
Where { $_.DateCreated -gt (get-date).AddDays(-7) }
Get-SqlAgentJob -ServerInstance localhost |gm -MemberType Properties| measure
$sqlserver = 'localhost\SQL2016'
Get-SqlAgent -ServerInstance localhost |
<# Looking at your output, if you're new to PowerShell you'll want to know... #>
Get-SqlAgentJobHistory -ServerInstance $sqlserver -Since MIDNIGHT | %{$Job = $_}
FT -AutoSize
Get-SqlAgentJob -ServerInstance localhost\SQL2016
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -Since Midnight -OutcomesType Failed |
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate 2017-01-25::20:00 -OutcomesType Failed
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate (get-date).AddDays(-4) -OutcomesType Failed
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed
$JobDate = (get-date).AddHours(-12)
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2016 -StartRunDate $JobDate -OutcomesType Failed
foreach ($ServerList IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Host )
Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed |
Format-Table -AutoSize
$datevar = Get-Date -Format FileDate
$ServerList = DIR -Recurse 'SQLSERVER:\SQLRegistration\Database Engine Server Group\Host'|
WHERE {$_.Mode -ne 'd'}
Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate -Since Midnight -OutcomesType Failed |
Get-SqlAgentJobHistory -ServerInstance $ServerList.Name -StartRunDate (get-date).AddHours(-12) -OutcomesType Failed |
$A = Get-Date
#| WHERE RunStatus -EQ 1 | ogv
Get-SqlAgentJobHistory -ServerInstance $sqlserver -Since LastMonth |
<# Check the history of all my Registered SQL Servers #>
foreach ($RegisteredSQLs IN dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Host |
where {$_.Mode -ne 'd'} | OGV -PassThru)
Get-SqlAgent -ServerInstance $RegisteredSQLs.Name |
Get-SqlAgentJob |
Where-Object { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }
<# This code will do the same thing as above.
Which to use is up to you and your situation.
If you wanted to do more than just display the results,
that could make you lean towards the loop. #>
Get-SqlAgent -ServerInstance localhost, localhost\SQL2016 |
Get-SqlAgentJob |
Where-Object { $_.IsEnabled -eq $true -and $_.LastRunOutcome -eq 'Failed' }
<# .DEMO Get-SQLErrors & Write-SqlTableData #>
Get-SqlErrorLog -ServerInstance localhost, localhost\SQL2016 -Since LastWeek |
Out-GridView -PassThru | <# Once this opens, type in something to filter on,
e.g. memory, backup, port...
after it filters do select all, and then hit OK #>
Write-SqlTableData -ServerInstance localhost\SQL2016 -DatabaseName tempdb -SchemaName dbo -TableName MemoryErrors -Force;
<# After creating the table, show that the rows also landed there. #>
Invoke-Sqlcmd -ServerInstance localhost\SQL2016 -Database tempdb -Query "
FROM dbo.MemoryErrors" -OutVariable MemErrors | measure
$MemErrors | SELECT *
<# Data Professionals want to be able to do _THIS_.
We want to be able to move logins from our old SQL instance to
our new SQL 2016 instance. #>
$a = Get-SqlLogin -ServerInstance localhost -LoginType WindowsUser <# SQL 2014 #>
$b = Get-SqlLogin -ServerInstance localhost\SQL2016 -LoginType WindowsUser <# SQL 2016 #>
<# This one works but requires a prompt to enter the password of the new user when copying a SQL Login #>
Compare-Object -ReferenceObject $b -DifferenceObject $a -PassThru | <# Use Compare-Object for missing logins #>
Out-GridView -PassThru |
foreach{ <# Out-GridView so you can filter #>
Add-SqlLogin -ServerInstance localhost\SQL2016 -LoginType $_.LoginType -LoginName $_.Name -DefaultDatabase $_.DefaultDatabase -Script
<# Make sure to run the command below to prove the login now exists on the 2016 instance #>
Get-SqlLogin -ServerInstance localhost\SQL2016 -LoginType WindowsUser
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment