Sunday, August 10, 2014

Windows 7 PowerShell Tutorial (Introduction to Windows 7 PowerShell)

What is Windows PowerShell?

Windows 7 powershell located at

      Start>All Programs>Accessories>Windows PowerShell>Windows PowerShell                   

 is command line tool which was built on top of .net framework. Windows powershell can be thought of as an advanced version of windows command prompt. (And PowerShell script is the advanced replacement for batch file) You can do a lot of things in powershell that you couldn't in the command prompt. In fact the industry push is towards using powershell for all windows computer administration tasks. Almost any windows administration task you can do thru a GUI, you can do it using powershell. The fact that command line tools barely change over time, makes it even more worth-while mastering them. The first version of powershell was released with Windows XP SP2 in 2006 as an optional add-on.

PowerShell Verbs and Nouns

All command-lets (commands) in powershell follow a verb-noun syntax. For example Get-Location, Set-Location, Get-Help, Clear-Host and so on. Once you understand this, you could guess many of the commands in PowerShell. PowerShell has a logical naming scheme for its commands.

Most common verbs used in powershell


Most common nouns used in powershell


Frequently used PowerShell command-lets (commands)

Most commonly used commands in powershell

Powershell Command-Let example
What does it do?

Get-Content myFile.txt
Prints out the contents of myFile.txt onto the console window
Lists all the items in the current directory (Same as dir in command prompt)
select-object Name, LastWriteTime |
sort-object LastWriteTime
Lists all the items in the current directory.
Only display their Name and LastWriteTime.
Order by LastWriteTime ascending
(The pipes are used to add conditions)
Copy-Item myFile.txt myFileCopy.txt
Creates a copy of myFile.txt and names that copy myFileCopy.txt
Invoke-item .
Open current directory
Clear screen. (Same as cls from command prompt)
Gives you short forms for various commands
set-alias list Get-ChildItem
Give Get-ChildItem an alias of list. In other words I am telling powershell, when I execute list what I mean is execute Get-ChildItem.
Note: Once you close the window the aliases you created are lost.
export-alias myAlias.csv
Export all aliases to a file called myAlias.csv
export-alias myAlias.csv
Import all the aliases in the file myAlias.csv
Gets all the available commands
Get-Command -verb "get"
Get all the commands with the verb “get” in it
For example Get-Host, Get-Process and so on
Get-Command -noun "job"

Get all the commands with the noun job in it.
For example Get-Job, Receive-Job and so on
Get-Command -type cmdlet
 Gets all the command-lets in powershell
Get-Help Get-Job -full
Gives you an explanation on what the command Get-Job does, explains to you its parameters and gives you some examples on how to use that command
Gets information about the current working location.
$test= "C:\Users\menonv\Desktop\myFile.txt"

(Get-Content $test).Replace("Hello","Hi")| Out-File  $test
In the file myFile.txt, replace Hello with Hi.
Gives you the value of the environment variable path
[Environment]::SetEnvironmentVariable( "NameOfTheVariable", "ValueOfTheVariable", [System.EnvironmentVariableTarget]::Machine )
Saves a system environment variable with the name NameOfTheVariable
and the
 value ValueOfTheVariable

Of all the command-lets above Get-Command And Get-Help are the most useful commands-lets. Thats why I have highlighted them.

PowerShell Providers

PowerShell providers are basically .net libraries for navigating some source (such as environment variables, registry, a remote computer and so on). To find all the providers run the command


You will see a result like this one (without the table :) )

Filter, ShouldProcess        
{C, D}          
ShouldProcess, Transactions  
{HKLM, HKCU}    

To navigate any of these sources you will have to "change your drive" to that provider. That's how the providers work in PowerShell. For example if I want to see all my environment variables this is what I would do

set-location Env:

PowerShell Snapins

We get access to providers via powershell snapins. To see a list of currently loaded sanpins run the command-let


You will see something like this

Name        : Microsoft.PowerShell.Diagnostics
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains Windows Eventing and Performance Counter cmdlets.

Name        : Microsoft.WSMan.Management
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains cmdlets (such as Get-WSManInstance and Set-WSManInstance) that are used by the Windows Pow
              erShell host to manage WSMan operations.

Name        : Microsoft.PowerShell.Core
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains cmdlets used to manage components of Windows PowerShell.

Name        : Microsoft.PowerShell.Utility
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains utility Cmdlets used to manipulate data.

Name        : Microsoft.PowerShell.Host
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains cmdlets (such as Start-Transcript and Stop-Transcript) that are provided for use with the
              Windows PowerShell console host.

Name        : Microsoft.PowerShell.Management
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains management cmdlets used to manage Windows components.

Name        : Microsoft.PowerShell.Security
PSVersion   : 2.0
Description : This Windows PowerShell snap-in contains cmdlets to manage Windows PowerShell security.

To see a list of snapins that are available but not loaded can be found by running

get-pssnapin -Registered   

You will see something like

Name        : SqlServerCmdletSnapin100
PSVersion   : 2.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin100
PSVersion   : 2.0
Description : SQL Server Provider

Name        : WDeploySnapin3.0
PSVersion   : 2.0
Description : This is a PowerShell snap-in that contains cmdlets for managing Microsoft Web Deployment infrastructure.

As you can see there are two snapins that I could load to access my sql server database

This is how you load those snapins

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

Now if you run the get-psprovider command, you will see that SQL Server is available on the drive "SQLSERVER"

Accessing SQL Server via powershell

Now that we load the SQL server snapin, we can access the local SQL server.

set-location   SQLSERVER:\

Now you will see something like this

PSPath        : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQL
PSParentPath  : SqlServerProviderSnapin100\SqlServer::SQLSERVER:
PSChildName   : SQL
PSDrive       : SQLSERVER
PSProvider    : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
Name          : SQL
Root          : SQLSERVER:\SQL
Description   : SQL Server Database Engine

PSPath        : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQLPolicy
PSParentPath  : SqlServerProviderSnapin100\SqlServer::SQLSERVER:
PSChildName   : SQLPolicy
PSDrive       : SQLSERVER
PSProvider    : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
Name          : SQLPolicy
Root          : SQLSERVER:\SQLPolicy
Description   : SQL Server Policy Management

PSPath        : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQLRegistration
PSParentPath  : SqlServerProviderSnapin100\SqlServer::SQLSERVER:
PSChildName   : SQLRegistration
PSDrive       : SQLSERVER
PSProvider    : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
Name          : SQLRegistration
Root          : SQLSERVER:\SQLRegistration
Description   : SQL Server Registrations

PSPath        : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\DataCollection
PSParentPath  : SqlServerProviderSnapin100\SqlServer::SQLSERVER:
PSChildName   : DataCollection
PSDrive       : SQLSERVER
PSProvider    : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
Name          : DataCollection
Root          : SQLSERVER:\DataCollection
Description   : SQL Server Data Collection

PSPath        : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\Utility
PSParentPath  : SqlServerProviderSnapin100\SqlServer::SQLSERVER:
PSChildName   : Utility
PSDrive       : SQLSERVER
PSProvider    : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
Name          : Utility
Root          : SQLSERVER:\Utility
Description   : SQL Server Utility

PSPath        : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\DAC
PSParentPath  : SqlServerProviderSnapin100\SqlServer::SQLSERVER:
PSChildName   : DAC
PSDrive       : SQLSERVER
PSProvider    : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
Name          : DAC
Root          : SQLSERVER:\DAC
Description   : SQL Server Data-Tier Application Component

Take a note of the PSChildName where the description is SQL server Database engine

set-location SQL

You will see something like this
PSPath : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQL\VIVEK-PC
PSParentPath : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQL
PSChildName : VIVEK-PC 
PSProvider : SqlServerProviderSnapin100\SqlServer
PSIsContainer : True
MachineName : VIVEK-PC
ManagedComputer : Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer
Servers : {[DEFAULT, Microsoft.SqlServer.Management.PowerShell.Extensions.ServerInformation]}

Take note of the PSChildName.

set-location VIVEK-PC

PSPath : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQL\VIVEK-PC\DEFAULT PSParentPath : SqlServerProviderSnapin100\SqlServer::SQLSERVER:\SQL\VIVEK-PC PSChildName : DEFAULT PSDrive : SQLSERVER PSProvider : SqlServerProviderSnapin100\SqlServer PSIsContainer : True DisplayName : DEFAULT AuditLevel : Failure BackupDirectory : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup BrowserServiceAccount : NT AUTHORITY\LOCALSERVICE BrowserStartMode : Disabled BuildClrVersionString : v2.0.50727 BuildNumber : 1617 Collation : SQL_Latin1_General_CP1_CI_AS CollationID : 872468488 ComparisonStyle : 196609 ComputerNamePhysicalNetBIOS : VIVEK-PC DefaultFile : DefaultLog : Edition : Developer Edition (64-bit) ErrorLogPath : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log FilestreamLevel : Disabled FilestreamShareName : MSSQLSERVER InstallDataDirectory : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL InstallSharedDirectory : c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL InstanceName : IsCaseSensitive : False IsClustered : False IsFullTextInstalled : True IsSingleUser : False Language : English (United States) LoginMode : Mixed MailProfile : MasterDBLogPath : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA MasterDBPath : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA MaxPrecision : 38 NamedPipesEnabled : False NetName : VIVEK-PC NumberOfLogFiles : -1 OSVersion : 6.1 (7601) PerfMonMode : None PhysicalMemory : 16298 PhysicalMemoryUsageInKB : 186024 Platform : NT x64 Processors : 4 ProcessorUsage : 0 Product : Microsoft SQL Server ProductLevel : RTM ResourceLastUpdateDateTime : 4/22/2011 2:40:54 PM ResourceVersionString : 10.50.1617 RootDirectory : c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL ServerType : Standalone ServiceAccount : NT AUTHORITY\NETWORKSERVICE ServiceInstanceId : MSSQL10_50.MSSQLSERVER ServiceName : MSSQLSERVER ServiceStartMode : Auto SqlCharSet : 1 SqlCharSetName : iso_1 SqlDomainGroup : Vivek-PC\SQLServerMSSQLUser$Vivek-PC$MSSQLSERVER SqlSortOrder : 52 SqlSortOrderName : nocase_iso Status : Offline TapeLoadWaitTime : -1 TcpEnabled : False VersionMajor : 10 VersionMinor : 50 VersionString : 10.50.1617.0 Name : VIVEK-PC Version : 10.50.1617 EngineEdition : EnterpriseOrDeveloper ResourceVersion : 10.50.1617 BuildClrVersion : 2.0.50727 DefaultTextMode : True Configuration : Microsoft.SqlServer.Management.Smo.Configuration AffinityInfo : Microsoft.SqlServer.Management.Smo.AffinityInfo ProxyAccount : [VIVEK-PC] Mail : [VIVEK-PC] Databases : {master, model, msdb, Play...} Endpoints : {Dedicated Admin Connection, TSQL Default TCP, TSQL Default VIA, TSQL Local Machine...} Languages : {Arabic, British, čeština, Dansk...} SystemMessages : {21, 21, 21, 21...} UserDefinedMessages : {} Credentials : {} CryptographicProviders : {} Logins : {##MS_PolicyEventProcessingLogin##, ##MS_PolicyTsqlExecutionLogin##, NT AUTHORITY\NETWORK SERVICE, NT AU THORITY\SYSTEM...} Roles : {bulkadmin, dbcreator, diskadmin, processadmin...} LinkedServers : {} SystemDataTypes : {bigint, binary, bit, char...} JobServer : [VIVEK-PC] ResourceGovernor : Microsoft.SqlServer.Management.Smo.ResourceGovernor ServiceMasterKey : Microsoft.SqlServer.Management.Smo.ServiceMasterKey Settings : Microsoft.SqlServer.Management.Smo.Settings Information : Microsoft.SqlServer.Management.Smo.Information UserOptions : Microsoft.SqlServer.Management.Smo.UserOptions BackupDevices : {} FullTextService : [VIVEK-PC] ActiveDirectory : Microsoft.SqlServer.Management.Smo.ServerActiveDirectory Triggers : {} Audits : {} ServerAuditSpecifications : {} ConnectionContext : server='VIVEK-PC';Trusted_Connection=true;Connect Timeout=30;Application Name='SQLPS (Vivek@VIVEK-PC)';m ultipleactiveresultsets=false Events : Microsoft.SqlServer.Management.Smo.ServerEvents OleDbProviderSettings : Urn : Server[@Name='VIVEK-PC'] Properties : {Name=AuditLevel/Type=Microsoft.SqlServer.Management.Smo.AuditLevel/Writable=True/Value=Failure, Name=Ba ckupDirectory/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL Server\MSSQL10_50.MS SQLSERVER\MSSQL\Backup, Name=BuildNumber/Type=System.Int32/Writable=False/Value=1617, Name=DefaultFile/T ype=System.String/Writable=True/Value=...} UserData : State : Existing

Note the PSChildName.

set-location DEFAULT


Running the command below will show you all the databases on your SQL server

set-location Databases
get-childitem | Select-Object PSChildName

No comments:

Post a Comment

Comments will appear once they have been approved by the moderator