Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
<#
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 #>
<#
Also, to get to the SqlServer PowerShell module
#>
Start-Process http://sqlps.io/sqlserver
<#
If you don't have PowerShell 5.0 / Windows MF 5 installed (https://www.microsoft.com/en-us/download/details.aspx?id=54616)
you will need SSMS 16.5.3 in order to follow along
#>
#Standard Naming, no abbreviations
<#Stevenh Owens List#>
CD D:\AirStatsCSVs
<#01#>
Dir
<# Sometimes when you pipe a command to format-list you will automatically get more properties #>
Dir | Format-List
<#03#>
Dir | SELECT *
<#04#>
MKDIR -Name CSVs -Path C:\temp\AirStats
<#05#>
Dir | Move-Item -Destination C:\temp\AirStats\CSVs
<# What If, does it help with Dynamic names? #>
Dir | Move-Item -Destination C:\temp\AirStats\CSVs -Force -WhatIf
<#07#>
while ($true) {Move-Item c:\temp\xyz.txt -PassThru}
<#08#>
Get-Service -ComputerName | Stop-Service
<#09#>
Get-Help
<#Aaron's Demo List#>
Get-Service -DisplayName SQL*
<#01#>
Get-Service -DisplayName SQL* | select name -Last 5
<#01#>
Get-Service -DisplayName SQL* |
WHERE {$_.Name -Match 'ReportServer' -and $_.CanStop} |
Sort -Property DisplayName
<#01#>
Get-Service -ComputerName a,b,c -Name SQLSERVERAGENT | Start-Service
<#02#>
<# PowerShell isn't supposed to have all these short names,
so where do we find out what they mean? #>
DIR Alias:\
DIR Alias:\%
DIR Alias:\% | fl
DIR Alias:\ | where {$_.ResolvedCommand -eq 'ForEach-Object'}
#Start with showing how to Get-Table
<#03#>
<#04#>
<#05#>
<#06#>
<#07#>
<#08#>
<#09#>
<#10#>
<#11#>
<#12#>
<#13#>
<#14#>
<#15#>
<#16#>
<#17#>
<#18#>
<#19#>
<#20#>
<#
Get-History is your friend!
#>
Get-History
function Get-History2
{
Get-History |
SELECT Id, @{Label="TotalRunningTime";Expression={$_.EndExecutionTime - $_.StartExecutionTime}}, CommandLine, ExecutionStatus, StartExecutionTime, EndExecutionTime
}
<#
Start-Transcript is even better!
#>
Start-Transcript -Path c:\temp\PowerShellTranscripts\
<# Where are my backups? #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\SQL2014).BackupDirectory
<# Backup all my databases now please #>
Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } |
Backup-SqlDatabase -CompressionOption On;
<# Next, a message from Grant #>
Start-Process https://youtu.be/Ah0jabU9G8o?t=2m56s
<# My Surface Book is so fast we probably can't see this happen #>
Invoke-Item (Get-Item SQLSERVER:\SQL\LOCALHOST\SQL2014).SQL2014File
<# Grant said it, so we better do it. #>
Test-DbaLastBackup -SqlServer localhost -Destination $new -VerifyOnly | Out-GridView
#Before
Remove-Module SqlServer;
Get-PSDrive
#Snapins that are running
Import-Module SQLServer
#Snapins that you can load
Get-Module
#New Resource
Get-PSDrive
CD SQLSERVER:\
DIR;
<# What's inside SQLRegistration? #>
CD SQLSERVER:\SQLRegistration
DIR;
CD 'Database Engine Server Group'
DIR;
CD Host
DIR;
<# What's inside that "SQL" folder? #>
CD SQLSERVER:\SQL\
DIR;
<# What's inside that computer? #>
CD 'localhost'
DIR;
<# Let's brab one of those instances and have a look around. #>
GET-ITEM 'SQL2016'
<# 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'
$MySQL2016Instance
$MySQL2016Instance.GetType()
<# Let's take a quick look at all of the databases #>
CD SQLSERVER:\SQL\localhost\SQL2016\databases\
DIR;
<# 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'
$bob.GetType()
$ralph = Get-ITEM 'AirStats' | SELECT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable
$ralph.GetType()
<# Let's take a quick look at all of my tables #>
CD SQLSERVER:\SQL\localhost\SQL2016\databases\AirStats\tables\
DIR;
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 |
Out-GridView
<# Let's take a quick look at all of my tables #>
DIR SQLSERVER:\sql\localhost\SQL2016\databases\AirStats\tables\ |
WHERE {$_.IndexSpaceUsed -gt $_.DataSpaceUsed} |
SELECT Schema, Name, DataSpaceUsed, IndexSpaceUsed, RowCount, HasCompressedPartitions, HasClusteredColumnStoreIndex |
Out-GridView
<# 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
CD SQLSERVER:\SQL\localhost\Default\DATABASES\ADVENTUREWORKS2014\TABLES
DIR
#Let's script this table out
$PTH = get-item Production.TransactionHistory
$PTH.Script()
#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?
$PTH.GetType()
<##########################
Now Let's talk about loops for a little bit.
##########################>
<# Dir gives a lot of results. #>
cd SQLServer:\sql\localhost\default\Databases\AdventureWorks2014\Tables\
dir
dir| measure
<# What if we wanted to do something to tables one at a time, like
maybe update their statistics.
There are multiple approaches to looping but some of the most common are:
A) foreach () {}
B) foreach {}
C) | %{} #>
foreach($Table IN dir)
{
"$Table"
}
<# So what, you've printed out the names of my tables #>
<# That's no moon. Er, That's no PRINT statement #>
$Table.GetType()
foreach($Table IN dir)
{
$Table.UpdateStatistics()
}
<# We can even see all the statistics if we want to. #>
foreach($Table IN dir)
{
$Table.Statistics
}
<# But that's not super helpful because have lost the context of
which statistic belongs to which table. #>
<# Ok, let's cycle through the whole database to look at those indexes #>
foreach($Table IN dir)
{
foreach($Index IN $Table.Indexes)
{
$Index | SELECT Name, Parent, FileGroup
}
}
<# We can take that same loop and make it even more useful #>
$Index | SELECT *
foreach($Table IN dir)
{
foreach($Index IN $Table.Indexes)
{
$Index | SELECT Name, Parent, FileGroup, SpaceUsed
}
}
<# Of course, it would be more helpful if we could just call a function to get that info. #>
function Get-SQLIndexInfo
{
foreach($Table IN dir)
{
foreach($Index IN $Table.Indexes)
{
$Index | SELECT Parent, Name, FileGroup, SpaceUsed
}
}
}
<##########################
Next up DATA_COMPRESSION!
##########################>
CD SQLSERVER:\sql\localhost\DEFAULT\Databases\AdventureWorks2014\Tables\;
DIR |
SELECT Schema, Name, HasCompressedPartitions, dataspaceused |
WHERE {$_.HasCompressedPartitions -eq $True} |
OGV -PassThru | %{
Invoke-SqlCmd -query "
ALTER TABLE $($_.Schema).$($_.Name) REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = None);
"}
#Invoke-SqlCmd -query "SELECT COUNT(*) FROM $($_.Schema).$($_.Name)"
<# Note!: This method does not trigger the SMO to update itself
which is one reason to avoid this approach if you can.
The other reason is SQL Injection. #>
DIR |
SELECT Schema, Name, HasCompressedPartitions, dataspaceused |
WHERE {$_.HasCompressedPartitions -eq $True}
<# Of course, you could do this with SMO code too. #>
dir |
Out-GridView -PassThru |
foreach {
foreach ($partition in $psitem.PhysicalPartitions)
{
$partition.DataCompression = "Row"
}
$_.Alter()
}
#But what about this table's childrens?
cd Production.TransactionHistory
dir
<# Now we can go exploring!
What interests you? #>
DIR SQLSERVER:\
Invoke-SqlCmd -query "select * from $(get-item .)"
<#
You have lots of profiles that you can configure.
We will focus on two for today.
#>
psedit $profile
psedit $profile.CurrentUserAllHosts
<#
#>
<#
This is how I get auto-completion for my parameter values
#>
<# First we need an argument completer for -ServerInstance #>
Register-ArgumentCompleter -ParameterName ServerInstance -ScriptBlock {
(ls -Recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ |
?{ $_.Mode -ne 'd'} |
Group-Object ServerName).Name | ForEach-Object {
$CompletionText = $_
New-Object System.Management.Automation.CompletionResult (
$CompletionText,
$_,
'ParameterValue',
"$_ (SQLInstance)"
)
}
};
<# When the Database Parameter is called -Name we do this #>
Register-ArgumentCompleter -ParameterName Name -ScriptBlock {
param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
#$global:__LastFakeBoundParameter = $fakeBoundParameter
if ($fakeBoundParameter.ContainsKey('ServerInstance')) {
(ls "SQLSERVER:\SQL\$($fakeBoundParameter.ServerInstance)\Databases").Name | ForEach-Object {
New-Object System.Management.Automation.CompletionResult (
$_,
$_,
'ParameterValue',
"$_ (Database)"
)
}
}
};
<# When the Parameter is called -Database we do this #>
Register-ArgumentCompleter -ParameterName Database -ScriptBlock {
param($commandName, $parameterName, $wordToComplete, $commandAst, $fakeBoundParameter)
if ($fakeBoundParameter.ContainsKey('ServerInstance')) {
(Get-SqlDatabase -ServerInstance $($fakeBoundParameter.ServerInstance)).Name | ForEach-Object {
$CompletionText = $_
New-Object System.Management.Automation.CompletionResult (
$CompletionText,
$_,
'ParameterValue',
"$_ (Database)"
)
}
}
};
<# When we run Get-SqlDatabase and point it at one of our SQL instances we get
output that is similar to the basics of Object Explorer Details (F7) in SSMS. #>
Get-SqlDatabase -ServerInstance Localhost\SQL2016
<# 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. #>
<# THESE NUANCES ARE CRITICAL!
SERIOUSLY.
YOU HAVE NO IDEA HOW MUCH TIME I HAVE LOST TO LITTLE THINGS LIKE THIS #>
<# When you use the -Name parameter to specify a database this works as expected. #>
$ralph = Get-SqlDatabase -ServerInstance Localhost\SQL2016 -Name AirStats
$ralph.GetType()
<# When you don't specify -Name you get all databases on the instance.
Since this is the Get-SqlDatabase cmdlet not the Get-SqlInstance cmdlet you
end up with a data type you weren't expecting. #>
$bob = Get-SqlDatabase -ServerInstance Localhost\SQL2016
$bob.GetType()
<# Inside of $bob are all the databases on the instance. #>
$bob | measure
$bob | %{$_}
<# Up-Vote this Connect Item if you want a working Get-SqlInstance cmdlet.
https://connect.microsoft.com/SQLServer/feedback/details/3005019 #>
<# Interestingly, even though $bob is an array, each item inside is a #>
$bob | %{$_.GetType()}
<# THESE NUANCES ARE CRITICAL! #>
<# Ok, let's make this data more interesting. #>
$bob | SELECT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable
<# How did I know that I could find the Size, DataSpaceUsage, IndexSpaceUsage,
& SpaceAvailable properties inside of $bob?
1) We can actually get some of this knowlege from OED (F7) in SSMS
2) In PowerShell the most direct method is to use the Get-Member cmdlet.
3) Since we're SQL-people you may prefer to use SELECT * #>
$bob | Get-Member
$bob | SELECT *
<# In this case, using SELECT * wasn't very user friendly because it ran #>
<# It is also handy to know that you can easyily grab a single property alomst
as if it were a subquery #>
(Get-SqlDatabase -ServerInstance Localhost\SQL2016).Name
$bob.GetType()
Get-Command -Module SqlServer -CommandType Cmdlet |
Sort -Property noun |
SELECT * -First 1
Get-Command -Module ReportingServicesTools | sort -Property name | Out-GridView
Get-Help -Name Get-SqlDatabase -ShowWindow
<# You can also see a list of commands and instructions at
https://github.com/Microsoft/ReportingServicesTools
Invoke-Expression (Invoke-WebRequest https://aka.ms/rstools) #>
<# Upload a single report #>
Get-Help -Full Write-RsCatalogItem
Write-RsCatalogItem -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Path 'C:\SSIS Reporting Pack\Reports\dashboard.rdl' -Destination /SSISReports
<# Upload an entire folder #>
Get-Help -Full Write-RsFolderContent
Write-RsFolderContent -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Path 'C:\SSIS Reporting Pack\Reports' -Destination /Sanjay
<# Still trying to figure this part out. #>
Get-Help -Full New-RsDataSource
Set-RsDataSource -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Name SSISCatalog -DataSourceDefinition 'Data Source=DESKTOP-52BPLTN\\SQL2016;Initial Catalog=SSISDB'
Get-Help -Full Set-RsDataSource
Set-RsDataSource -ReportServerUri 'http://desktop-52bpltn/ReportServer_SQL2016' -Name SSISCatalog -DataSourceDefinition 'Data Source=DESKTOP-52BPLTN\\SQL2016;Initial Catalog=SSISDB'
(Get-Item sqlserver:\SQLAS\localhost\default\).BackupDirectory
(Get-Item sqlserver:\SQLAS\localhost\SQL2016\).ServerProperties |
WHERE {$_.Value -ne $_.DefaultValue} | OGV
<# Demo code #>
Import-Module SqlServer
(Get-Item sqlserver:\SQLAS\localhost\Default\).ServerProperties |
WHERE {$_.Value -ne $_.DefaultValue} | OGV
<# /Demo code #>
Get-Item sqlserver:\SQLAS\SurfaceBookSQL\SQL2016\ | GM
Get-Help Compare-Object
$o3 = (Get-Item SQLSERVER:\SQLAS\ServerA\Default\).ServerProperties | SELECT Name, Value
$o2 = (Get-Item SQLSERVER:\SQLAS\ServerB\Default\).ServerProperties | SELECT Name, Value
Compare-Object -ReferenceObject $o3 -DifferenceObject $o2 -IncludeEqual -Property Name
<# Will this allow you to compare settings between two SSAS instances? #>
$o3 = (Get-Item SQLSERVER:\SQLAS\ServerA\Default\).ServerProperties
$o2 = (Get-Item SQLSERVER:\SQLAS\ServerB\Default\).ServerProperties
Compare-Object -ReferenceObject $o3 -DifferenceObject $o2 -IncludeEqual -Property Name
<# SSAS Database Properties #>
(
Get-Item SQLSERVER:\SQLAS\LOCALHOST\SQL2016\Databases\AdventureWorksDW2014Multidimensional-EE\ | GM -MemberType Properties
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.