A simple note on "Scripting in Powershell"

3.5. A few notes on when to use curly brackets "{ }" and parenthesis "( )".

In the scripts sofar, we have seen the use of curly brackets "{ }" and parenthesis "( )" in various "places".
But there has to exist certain rules for this. That's indeed true.

Sometimes it "feels" quite intuitive, the more so if you already have use other scripting/programming languages.

For example a "statement block" always goes between curly brackets "{ }". For example:

$filelist=ls *.txt
foreach ($a in $filelist)
{
write-output $a
}

Although the "statement block" here, is just one statement, the "write-output $a" sits between the "{ }"

The simple example above, also shows a clear example when using parenthesis "( )".
The "foreach" loop must process a list, and this list is a sort of "control structure" for the loop on where to start,
and when to end the loop. In this case, the controlstructure is just a list of all .txt files.

But this is typical for loop structures. Anytime a list or expression is used to control a loop structure, it'sits between "( )".
Here, take a look at this while loop:

$i=1
while ($i -le 5) {write-output "Hi, here is $i";$i++}

But there are occasions where the use of curly brackets "{ }" and parenthesis "( )" might not be so obvious.

For example, when you have a CmdLet that pipes to another CmdLet, where the second CmdLet must evaluate a condition or expression,
then curly brackets "{ }" are used.

Here are a few examples:

=> For each device in the printer class, only show it's name:

Get-WmiObject Win32_Printer | ForEach-Object {$_.Name}

=> Only print the properties for this one printer, called "Fax":

Get-Wmiobject Win32_printer | where {$_.name -eq "Fax"}

=> Again an example where a CmdLet (Get-Childitem) pushes records to "Where" (an alias to Where-Object), which must evaluate something.

function findfile ($p, $f)
{
Get-Childitem $p -Recurse | Where {$_.Name -eq "$f"}
}

Whe have seen the use of square brackets "[ ]" too, in a few occasions. They are often used to denote array members.

3.6. WMI.

WMI or "Windows Management Instrumentation", abstracts computer resources (like disks, memory, netcards etc..), and
anything else for which a socalled "WMI providers" exists.
For example, if there should exists a "provider" to see "scheduled tasks", then this resource is viewable from the WMI api too.
So, it's not only hardware resources that are covered by WMI, but applications and Operating System resources as well.

WMI is Microsoft's implementation of the general "CIM" standard ("Common Information Model").

In WMI language, the stuff that is managed and viewable, are the "Objects", which (as usual) are organized "in" "Classes".
The "script" or "programming language" that wants to access the managed objects, are often called "consumers".
These can access managed objects through the use of the right "providers".

The WMI infrastructure is fairly complex, and is partly also "COM" based. For this note, we do not need to know all of the details.
Important is, that Powershell CmdLets are available to query the WMI repository, which can obtain lots of systemdata.

The WMI repository uses hierarchical namespaces. Every set of objects which are related, gets their own "namespace".
Each namespace has a sort of "root". The default "root", if Powershell accesses WMI, is "root\cimv2", which is a collection
of classes which represents your computer system as a whole. However, usually other namespaces exists on your system as well,
like for example related to "applications", or a Directory Service etc..

The most important CmdLet is "Get-WmiObject", which can query namespaces, and all classes within a namespace (which is a class itself as well).

Let's try this:

PS C:\TEST> Get-WmiObject -namespace "root\cimv2" -List

This should return an impressive list of classes which you can query further.

Since the default namespace is "root\cimv2", you do not need to put that as a parameter if you want to get information
of the classes shown in the above listing. You can just query the classes directly. Here are a few examples:

PS C:\TEST> Get-WmiObject -Class win32_LogicalDisk
PS C:\TEST> Get-Wmiobject -Class win32_OperatingSystem
PS C:\TEST> Get-WmiObject -Class Win32_NetworkAdapter
PS C:\TEST> Get-WmiObject -Class Win32_Printer

Since an Object (like a printer) might have many "properies" (each with a value), you might be interrested in one property only.
In such a case, for printer objects, you can pass the records through to (for example) the "ForEach-Object" CmdLet,
and let it only print the "Name" (of each printer).

Remember: Every record transferred by "Get-WmiObject", is contained in the "$_" variable, which is always how it works
when two CmdLets are connected by a pipe. So, if you only want the printername, then get the "$_.name" property for each printer in the list.

PS C:\test> Get-WmiObject Win32_Printer | ForEach-Object {$_.Name}

Chapter 4. Some Further examples.

In time, for further "inspiration", I will add a couple of more scripts here, with appropriate comments.
For now, I think that already a few nice examples are listed. You can try them, or just simply browse along the code.
If you would agree (understand) the logic and syntax..., that only would already be fantastic !

---

Example 4.1: delete files from a folder (and subfolders) which are older than "X" days.

---

The problem can be defined as follows:

Many applications create logfiles. But after weeks, or months, those logfiles piles up, and take too much diskspace.
Maybe you only want to have the last 7 days, or 30 days (or so), online on disk.
So, older logfiles can be deleted. The following PS script does that.
You can schedule such a script, for example, to run once a day, once a week etc..

$Today = Get-Date
$NumberOfDays = "7"
$StartingFolder = "C:\temp"
$Extension = "*.log"
$LastWrite = $Today.AddDays(-$NumberOfDays)

# Body of code:

$FilesCollection = Get-Childitem $StartingFolder -Include $Extension -Recurse | Where {$_.LastWriteTime -le "$LastWrite"}

foreach ($File in $FilesCollection)
{
Remove-Item $File.FullName | out-null
}

Now, let's walk through this script.

You see that first a few variables are set. You know that any variable starts with a "$" symbol.
The variable $Today equals the CmdLet "Get-Date", which, when called, retrieves the current date/time.
Then a few fixed variables are set, like "$StartingFolder", which I placed here at "C:\TEMP".

The determination of "$LastWrite" deserves some explanation. You know that the plan is to remove all files
which are older that 7 days. To get at that point, we need a calculation later on, where the "date of 7 days ago"
must be known. Then we can investigate the "last write time" of any file, and compare it to the "date of 7 days ago".

You can try the following statements (or just read them...)

PS C:\TEST> $Today=Get-Date
PS C:\TEST> write-host $Today # lets see what we have

05.01.2013 13:25:02

PS C:\TEST> $NumberOfDays="7"
PS C:\TEST> $LastWrite = $Today.AddDays(-$NumberOfDays)
PS C:\TEST> write-host $LastWrite

29.12.2012 13:25:02

Yes, "$LastWrite" contains the "date", from 7 days ago.

Next, with "$FilesCollection", we create a collection (or list) of files, in all subfolders in C:\TEMP,
which are older than 7 days. Notice the use of the "Get-Childitem" CmdLet.
We have seen "Get-Childitem" before in example 6. Here too, we wanted a list of files. The name "Get-Childitem"
already says it all. If "C:\TEMP" is the "parent", then every object in it, are it's "children".

You should be able to have "an idea" how "$FilesCollection", after "Get-Childitem" did it's work, is a list of files
which must be processed. Next, a foreach loop will work through the list, and deletes the files.

Note:

A similar cmd shell command is:

C:\> forfiles /P C:\webserver\logs /S /M *.log /D -30 /C "cmd /c del @FILE"

This example will delete all *.log files in the "c:\webserver\logs" directory, which are older than 30 days.
It seems almost easier than using Powershell ! Well, No. You do not want to know what effort MS used to build the "forfiles" command.
With Powershell, your CmdLets and everything else that make up the environment (e.g. loop constructs, functions etc..)
all form endlessly more powerfull building blocks, to build anything you need.

---

Example 4.2: Viewing and managing a "Win2K8 / Win2K12 Failover Cluster".

---

Once you have installed a "Failover Cluster" on 2 or more Nodes, on each node, new Failover cluster CmdLets
are available on those systems.

However, it's possible you still need to load the failoverclusters powershell module before you can use those new CmdLets.

PS C:\> import-module failoverclusters

Let's take a look at a few of those new CmdLets. Here are some examples.
The commands shown, are only for illustrational purposes. In your particular situation, you often need to modify
such an example, before it really works (e.g. clustername, resource group name etc...), unless it's a generic command
which does not use a particular cluster object name (for example, create a Cluster log file).

=> 1. Create the Cluster log file:

Examples:

- Create the log into the current directory (.), over the last 400 minutes (that could be a large ascii file).

PS C:\> Get-ClusterLog -Destination . -TimeSpan 400

- Create the log into the current directory, over the last 10 minutes.

PS C:\> Get-ClusterLog -Destination . -TimeSpan 10

=> 2. Show cluster nodes:

PS C:\> Get-ClusterNode

Name..........State
----..........-----
node1.........Up
node2.........Up

If you are not connected to a certain Node of a certain cluster, or you want to see the state of the nodes
of another cluster, then supply the "clustername" to the CmdLet.

PS C:\> Get-ClusterNode -Cluster ClusterSales

=> 3. Show info of a Cluster (or names of all Clusters)

PS C:\> Get-Cluster
PS C:\> Get-Cluster cluster1
PS C:\> Get-Cluster | fl * (shows lots of info)
PS C:\> Get-Cluster -domain antapex.nl

=> 4. Show cluster quorum info:

PS C:\> Get-ClusterQuorum -Cluster SQLCluster1

=> 5. Move (failover) a Group to another node:

PS C:\> Move-ClusterGroup –Name SQLCLUS1 -Node node2
PS C:\> Get-ClusterNode node2 | Get-ClusterGroup | Move-ClusterGroup

=> 6. view resources for a certain Group:

PS C:\> Get-ClusterGroup "GROUPNAME" | Get-ClusterResource

=> 7. Start/Stop a certain Group:

PS C:\> Start-ClusterGroup SQLCLUS1
PS C:\> Stop-ClusterGroup SQLCLUS1

---

Example 4.3: Put quotes around the records of a (flat) .csv file.

---

Suppose that from a database, a report is extracted using some export utility. This utility produces a ".csv" file,
which is a "comma seperated" ascii file. Suppose that this export file is called "pre_report.csv" and it is created by
the export utility in the folder "F:\Uploads\".

Now, suppose that the records in "pre_report.csv" have a layout as in the following example:

Business Unit,Revenue,TotalCosts
NL,1000400,200562
US,200726,19925
BE,5999000,2001788
etc..

Generally, there could be hundreds, or thousends (or even millions) of rows in such a file.
Anyway, suppose further that the file is not complete for further processing.
What the requirement is, is that the records shown above, needs to be enclosed (surrounded) by quotes (").
(For the sake of argument, let's suppose that the database extracting utility is not able to do this).

So, we need this:

"Business Unit,Revenue,TotalCosts"
"NL,1000400,200562"
"US,200726,19925"
"BE,5999000,2001788"
etc..

One solution is, that we create a PowerShell script that operates on every record in "pre_report.csv",
put double quotes around every record, and places them in the new "report.csv" .csv file.
Take a look at the following script, which will do exactly that.

Remove-Item F:\Uploads\report.csv

$sym="`""
$list = Get-Content F:\Uploads\pre_report.csv

foreach ($v in $list)
{
Write-Output "$sym$v$sym" >> F:\Uploads\report.csv
}

As you can see, using the "Remove-Item" CmdLet, we start with removing the former "report.csv" file.

Next, we use a variable "$sym" which contains the " symbol. Such a character is a string really, so you would
normally enclose it using double quotes. But just using three double quotes (""") does not work.
Using it this way "`"", the special single quote will sort of "hide", or "escape", the second double quote, so it ends up
as if the second quote is just a normal string (enclosed by double quotes).

Next, we use the "Get-Content" CmdLet, to read in the "pre_report.csv", and put it in the $list variable.
Next, using the "foreach" loop, we process every record from "$list", meaning that we place double quotes
around every record, and store this new collection in the file "report.csv".

---

Example 4.4: Some AD examples.

---

When working on a Win2K8 DC, you likely have the "Active Directory" module available, which means that new specialized CmdLets are
at your disposal, from which you can easily add, delete, list accounts, groups, members in groups, computers, list OU's, etc.. etc..

With some preparations done, you can also use it from a Win7 workstation, or some management Server.

Here are a few simple examples.

List all user accounts in AD with their most interesting properties (like CN, enabled/disabled etc..):

get-aduser –filter * | export-csv "listing.csv"


List all user accounts in groups, with group names like FIN* (like Finance or any group else starting with "FIN":

# Users in All FIN* groups in user "Long" names (Distinguished Names)
# First get all groups with names like FIN*
$groups=get-ADGroup -Filter { name -like 'FIN*' } -Searchbase "DC=myorg,DC=org" | ForEach-Object {$_.SamAccountName}

# Find all Members in those FIN* groups:
foreach ($group in $groups){
get-adgroupmember $group | foreach-object { $_.Name} >> FINmembersFullNames.txt
}


# Same script, but now Members of All FIN* groups in in Short names (SAM Names)
# First get all groups with names like FIN*
$groups=get-ADGroup -Filter { name -like 'FIN*' } -Searchbase "DC=myorg,DC=org" | ForEach-Object {$_.SamAccountName}

# Find all Members in those FIN* groups:
foreach ($group in $groups){
get-adgroupmember $group | foreach-object { $_.SamAccountName} >> FINmembersSAMNames.txt
}

---

Example 4.5: Loop through a text file with accounts and assign file system permissions.

---

Suppose you have a text file with user accounts and other data. Now, using that file, you need to grant
permissions to some filesystem directory, using "icacls", for the user accounts that are listed in that file.

Just suppose we need to create user home directories, and give every user full control on it's private directory,
as well as making the user the "owner" of it's private directory.


Suppose we have the following file, called "newaccounts.txt":

12335 user1 Amsterdam
23769 user2 New York
67244 user3 Berlin

Then, using

Get-Content newaccounts.txt | %{$data=$_.split(" "); Write-Output "$($data[1])"} > acctmp.txt

produces the file "acctmp.txt", with content like this:

user1
user2
user3

Using "Write-Output "$($data[1])"}", only the second column (column #1 since the array starts with "0") will be printed.

We can make the code better, if we first test if a former "acctmp.txt" already exists, and if so, delete it first.
So, we could start with this block:

if (Test-Path c:\test\acctmp.txt)
{
Remove-Item c:\test\acctmp.txt
}

So, the CmdLet "Test-Path" can be used to test if a directory or file exists. If "true" the code between { } will be executed.

Note that the "test" is actually useless in our case, since later on we create "acctmp.txt" using single redirection ">",
which will always overwrite a former file (if it existed already).
However, in many other scripts the CmdLet "Test-Path" can be very usefull.

Now, our full script could look like this:

if (Test-Path c:\test\acctmp.txt)
{
Remove-Item c:\test\acctmp.txt
}

Get-Content newaccounts.txt | %{$data=$_.split(" "); Write-Output "$($data[1])"} > acctmp.txt

$list = Get-Content c:\test\acctmp.txt

foreach ($login in $list)
{
New-Item \\SERVER\users\$login -itemtype directory

$acl = Get-Acl \\SERVER\users\$login
$permission = "YOURDOMAIN\$login","FullControl","Allow"
$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule $permission
$acl.SetAccessRule($accessRule)

$acl | Set-Acl \\SERVER\users\$login
icacls \\SERVER\users\$login /setowner "YOURDOMAIN\$login" /T
}

The first few lines we have already discussed.

Next, we start a loop using the records in "$list" which contains all accounts from "acctmp.txt".

Using the CmdLet "New-Item" we create the users private Home directory.

Then, we define a series of variables which essentialy are use to transfer the value "FullControl"
to the second last statement, where "Set-Acl" is "feeded" with "FullControl" which gets applied to
the user's Home dir "\\SERVER\users\$login".

Finally, the last statement uses "icacls" to make the user the owner of it's private Home dir.

---

Example 4.6: Compare txt files (.txt, .csv etc...)

---

There are ofcourse many ways to do that. In one approach, we might use the Compare-Object CmdLet.
Here are just a few examples (or no more than a few statements, actually):

PS C:\temp> Compare-Object -ReferenceObject (Get-Content .\ReportJohn.csv) -DifferenceObject (Get-Content .\ReportMary.csv)

other similar example (could be in a script):

$RefFile = Get-Content "C:\Temp\ReportJohn.txt"
$DiffFile = Get-Content "C:\Temp\ReportMary.txt"
Compare-Object $RefFile $DiffFile

So, if you would have "C:\Temp\ReportJohn.txt" as having the content:

a
b
c
d
e

And if you would have "C:\Temp\ReportMary.txt" as having the content:

a
b
c

Then running the command (or script) would show you "d" and "e" as the difference.

Note: the DOS "fc" command in order to compare (ascii or binary) files, is really very easy to use:

C:\TEMP> fc /L ReportJohn.txt ReportMary.txt

Note: Use "fc /?" to get more info on the command switches.

Indeed, in just a few cases DOS commands work even better than the equivalent Powershell.
But I haste me to say that PowerShell is much more "powerful" and has a much wider scope, compared to DOS.

If you look at the last powershell example, and put those 3 lines in a ".ps1" scriptfile, then it works for the
ReportJohn.txt and ReportMary.txt files, But that's quite ugly. It's much better to have a script
that uses parameters to be supplied, so that you can re-use it for other (similar) files as well.
Do you know how to do that? Yes..., if you indeed have read this note, you do.

---

Example 4.7: Calling a stored procedure, or starting a SQL Agent job, from Powershell. (Works, but a bit outdated).

---

The following shows a very simple example. You need to know which stored procedure you want to call,
or which SQL Server Agent job you want to start, and ofcourse which database and Instance are involved.

Example:

$instancename="MySQLServer"
$db="MyDataBse"

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText= "exec msdb.dbo.sp_start_job 'My SQL Agent job'"
Write-Host "Executing the job..."
$result = $sqlCommand.ExecuteNonQuery()
$sqlConnection.Close()

In the example below we use a call to a stored procedure from Powershell.
Here we call the stored procedure "msdb.dbo.performancevlpi , which performs some stuff,
but also returning a variable which contains the end status of the performed work.

$Logfile = "\\fileshare\dfs\jobs\albert.log"
$instancename="SVRSQL05"
$db="DataMart"
$myoutput=""

Write-Host "Job is running. Please do not close this window"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db
$sqlConnection.Open()
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 3600
$SqlCommand.CommandText = "msdb.dbo.performancevlpi"
$SqlCommand.Connection = $SqlConnection
$SqlCommand.CommandType = [System.Data.CommandType]'StoredProcedure';
$outParameter = new-object System.Data.SqlClient.SqlParameter;
$outParameter.ParameterName = "@answer";
$outParameter.Direction = [System.Data.ParameterDirection]'Output';
$outParameter.DbType = [System.Data.DbType]'String';
$outParameter.Size = 2500;
$SqlCommand.Parameters.Add($outParameter) >> $null;

$result = $SqlCommand.ExecuteNonQuery();
$myoutput = $SqlCommand.Parameters["@answer"].Value;
$myoutput;
$SqlConnection.Close();

If ($myoutput -eq 1)
{
Write-Output "The job completed succesfully" | Out-File $logfile
}
else
{
Write-Output "The job failed" | Out-File $logfile
}

---

Example 4.8: Finding the most recent file in some folder.

---

Example 1:

$dir = "c:\test"
$latest = Get-ChildItem -Path $dir | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$latest.name

Example 2:

$f = Dir | Sort CreationTime -Descending | Select Name -First 1
$f.name

Example In DOS (cmd):

Suppose we are using d:\test:

FOR /F "delims=|" %%I IN ('DIR "d:\test\*.*" /B /O:D') DO SET NewestFile=%%I
echo %NewestFile%

---

4.9: Rename file(s).

---

Example 1:

$datetime = get-date -f MMddyy-hhmmtt
rename-item d:\test\daily.bak -newname ($datetime + ".bak")

Example 2:

Get-ChildItem -Filter “*.bak” -Recurse | Rename-Item -NewName {$_.name -replace '.bak','.archive' }

Get-ChildItem *.dmp | Rename-Item -NewName { $_.Name -replace '.dmp','.bak' }

Example In DOS (cmd):

ren *.doc *.archive
ren a.txt b.txt
ren f:\data\sales.xls sales_2015.xls (or better: copy f:\data\sales.xls g:\backups\sales_2015.xls)

---

4.10: "Tail" or Show, the end of large txt files (like large logfiles)..

---

In unix/linux, it's easy to see the last lines of some txt file (like a logfile). Simply use the tail command, like:

$ tail -50 alert.log

which shows you the last 50 lines of that logfile. You can use any number, like 50, 120, 200 etc..

In Powershell we have a similar commandlet. Use "Get-Content" with the "-tail" option.

Example:

PS C:\LOGS> get-content -tail 100 '.\web.log'

Here, the ".\" means that the CommandLet must use the current directory.

Chapter 5. Powershell and SQL Server.

5.1 Powershell versions

It seems that every new Windows version, comes with a new Powershell version. Also, in between, sometimes "interfaces"
to applications, and the OS, may be "upgraded" too. Backward compatibility of new versions to older scripts is very high,
although in some cases you must indeed choose to use a newer interface.


For about Windows versions, the following crude overview holds:

Version............Most prominent OS, in which it was first implemented
PowerShell 1.0.... Windows Server 2008, and Later XP versions (XP2)
PowerShell 2.0.... Windows Server 2008 and R2, Win7
PowerShell 3.0.... Windows Server 2012, Win8, Win7 SP1
PowerShell 4.0.... Windows Server 2012 R2, Win7, Win8.1
PowerShell 5.0.... Windows Server 2016, Win10
PowerShell 5.1.... Windows Server 2016, Win10 latest build
PowerShell 6.0.... comes available Win/Linux

If you look at figure 1 above, you see that Powershell itself runs on .Net, and has
a strong dependency on "WMI", that is Windows Management Instrumentation.
Through WMI, Powershell accesses objects of the OS and registered devices.

For example, although PowerShell 5 and WMI 5 are not equivalent, it is true that a more modern
Powershell, like Powershell v5, needs a certain revision of WMI too. It means also that you can use for example,
Powershell v5 on Win7, but then you need updates on WMI and .Net too.

You can check your version using:

PS C:\TEMP> $PSVersionTable.PSVersion

returns compact information on builds

PS C:\TEMP> get-host

returns information general version and language (culture).

If the variable $PSVersionTable.PSVersion is not available, you have v1/v2, and you may check that with "get-version".

As of SQL Server 2008, "direct" interfacing from Powershell to SQL Server, was possible, using
the sqlps.exe "wrapper", which starts Powershell, with the PowerShell snap-ins to get loaded and registered.
From that point, you could for example use the "Invoke-Sqlcmd" CmdLet.

At later SQL Server versions, as of 2012, the "sqlps" module could be loaded, enabling a larger set
of CmdLet's. As of SQL 2016, this module was enhanced and renamed to "SqlServer".

5.2 Powershell interfaces to SQL Server.

ADO.NET:

Since .Net became popular, ADO.NET is a "basic database API" to access a variety of RDBM'ses, like Oracle and SQL.
The namespace (provider) "System.Data.SqlClient", is well known by .NET developers.
So, for the older SQL Server 2008 version, for example, it was quite common to develop web apps, or C#, or VB .NET apps,
using ADO.NET to access databases.

ADO.NET can be used by various development platforms, and by Powershell too, like for example in the basic code below:

$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=SQL01\SQLINST1;Initial Catalog=sales;Connection Timeout=0;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandText = "SELECT * FROM customers"
$cmd.CommandTimeout = 0

Basically, example 4.7 above, uses ADO.NET too.
The method can be used by modern apps and modern SQL versions too. But Powershell has evolved a bit,
so other methods for access to databases, using rather simple CmdLet's, is possible too.

The older SQLPS and newer SqlServer modules:

With later SQL Server versions, using the graphical Management Studio, you can activate Powershell,
or actually "sqlps".
This module makes it possible to manage SQL Server objects from Powershell, and "traverse" the SQL Server hierarchy
of objects, just like a "filesystem" (using cd, ls etc..).

From the powershell "shell" itself, you can load the module too. Then use:

PS C:\TEMP> Import-Module -Name sqlps -DisableNameChecking

Apart from the feature that you can "walk" or "traverse" the SQL Server hierarchy, it also
makes a range of SQLserver CmdLet's available, like "Backup-Sql​Database", "Restore-Sql​Database" and many more.

SQLPS uses the "SQL Server Management Object" interface, or "SMO".

There are actually two SQL Server PowerShell modules, namely "SqlServer" (newer) and "SQLPS" (older).
The SQLPS module is/was included with the SQL Server installation, but is nowadays regarded
as "the former module". The most up-to-date PowerShell module today is the "SqlServer" module.
However, for the "SqlServer" installable module, Powershell v5 seems to be a requirement.

With SQL 2016, the "SqlServer" module seems to be automatically installed with the seperate SSMS (Management Studio) installation.
In most cases, it seems to reside in "%Program Files\WindowsPowerShell\Modules\SqlServer".

In that case simply give the command:

PS C:\TEMP> Import-Module -Name SqlServer

5.3 Powershell Architecture with API's .

Below you see a Jip-Janneke figure, trying to illustrate how powershell might access
other parts of the system (like the OS, Applications, SQL Server etc..).
The first sub-figure, is the same as figure 1, above. The second sub-figure, tries to show
the different API's to connect to SQL Server.

Fig 2. Simplified architecture Powershell.



Both figures are simply a very high-level overview. For example, the details of "WMI" are not shown.

5.4 SQL CmdLet's .

If you have imported "sqlps" or "SqlServer" modules, as shown above, a range of SQL Server specific
CmdLet's are available.

Example:

PS SQLSERVER:\SQL\Computer\Instance> Backup-SqlDatabase -Database "SALES"

To see all installed CmdLet's, try:

PS SQLSERVER:\SQL\Computer\Instance> get-command -module SQLSERVER

An overview of all CmdLet's from the imported "SqlServer" module, can be found in this Microsoft page:

SQL Server PowerShell cmdlets

That's it. Hope you liked it !