Introduction to Windows PowerShell for the SQL Server DBA Part 2

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | > PowerShell


I have been hearing about Microsoft's scripting language called Windows PowerShell which is extensively used nowadays for administering and managing Windows servers and workstations. Exchange Server 2007 and Windows Server 2008 are just two of the Microsoft server products that have included this as part of their administrative tools. I want to know how Windows PowerShell can help me as a SQL Server DBA.


To continue this series on Introduction to Windows PowerShell for the SQL Server DBA, this tip will look at the pipeline and output processing. In Part 1, we have introduced Windows PowerShell, the use of cmdlets and aliases and how we can take full advantage of the help available from the environment. We will build on what we have learned from the previous tip as we go along.

So, pipes are not just for plumbers

Pipes and piping are very common terms in any scripting language and it's no different in PowerShell. Think of it as a way to combine different commands (or in this case cmdlets) to do more complex operations. It works by directly connecting the output of the previous cmdlet to the input of the cmdlet that follows after it. As the output-input stream flows thru the pipe, they come in the form of objects and everything is being handled by the shell. As your operations become more complex, you can increase the number of pipes as necessary. To demonstrate, we will build on a few of the examples we have in part 1.

Let's use the Get-Command cmdlet to list all of the cmdlets available in Windows PowerShell that begin with the verb Get-. Since the Get-Command will list all of the cmdlets, we need to filter that list to only display what we need - cmdlets that start with the verb Get-. We will use the pipeline to connect this output to another cmdlet, Where-Object. The Where-Object cmdlet will act as our filter to display only those cmdlets we need. The syntax for this will be Get-Command | Where-Object {$_.Name -like "Get-*"}

powershell command

Now, don't be intimidated by the $_ you saw inside the curly brackets. Let me explain the contents of the script. You already know both the Get-Command and the Where-Object cmdlets so let me start with the $_. Basically, it is just a notation to represent the default object as it is being passed to the pipeline. Since we are passing a list of objects to the next cmdlet using the pipeline, the value of the $_ changes from one object being passed to another. The Name is an attribute/property of the object that we can access and query (or change depending on how the class was designed) as part of our processing. This could be anything depending on the objects that are being returned by the cmdlet but since we are dealing with the objects returned by the Get-Command cmdlet, we know there is a Name property. Now, I know you're starting to ask, "How do I remember all of the properties and methods of any objects that I will be using within PowerShell?" The answer is, you simply don't. You use the available help system we have talked about in part 1 and introduce a new cmdlet, Get-Member. The Get-Member cmdlet retrieves the properties and methods of objects passed to it. To use this cmdlet, just pipe the results of a cmdlet to Get-Member, like this:

Get-Command | Get-Member
powershell command

The -like keyword is an example of a comparison operator in Windows PowerShell. A list of these comparison operators is shown below

    • -lt: Less than
    • -le : Less than or equal to
    • -gt: Greater than
    • -ge : Greater than or equal to
    • -eq : Equal to
    • -ne : Not equal to
    • -like : Like; uses wildcards for pattern matching

You can mix and match these comparison operators and piping to come up with more complex tasks. Sorting and grouping are also available using the Sort-Object and Group-Object cmdlets, respectively. In one particular case as a SQL Server DBA, you may want to restart all SQL Server services in a machine after maintenance. You might be thinking of manually restarting all of them but think about having like 10 or more instances in a server. Even a NET START/NET STOP wouldn't be that helpful especially if you don't know the instance names of the SQL Server instances. You can use Windows PowerShell to do the trick, where the -Force parameter simply does the same thing for dependent services.

Get-Service | Where-Object {$_.Name -like "MSSQL$*"} | ReStart-Service -Force

Getting more aesthetic results

Whether we like it or not, having any output formatted is one of the most common things we do in our daily tasks, especially when whatever you will be doing will be read or validated by somebody else. By default, PowerShell takes care of the formatting for us. We can, however, decide which formatting to use with our output results. To get a list of the available formatting options in PowerShell, you can run

Get-Command Format-*
powershell command

These available cmdlets allow us to format the results of our scripts depending on our needs. To illustrate, let's use the Format-List cmdlet to display all the processes running in the local computer that are consuming more than 50MB of memory. Without formatting, this is how the results would look like running

Get-Process | Where-Object {$_.PrivateMemorySize -gt 50MB}
powershell command

Using the Format-List cmdlet, we can just display the ProcessName and PM(K) and probably the CPU(s) columns in the output running

Get-Process | Where-Object {$_.PrivateMemorySize -gt 50MB} | SELECT ProcessName, PrivateMemorySize, CPU | Format-List
powershell command

This is how the results would look like using the Format-Table cmdlet,

powershell command

You can try playing around with the other formatting options available in Windows PowerShell to suit your needs. Now, there might be cases where you need to store these results in a file. I normally export results in a CSV format so I can open the file in Microsoft Excel and present it as a report to management. Windows PowerShell has the Export-CSV cmdlet that allow you to create a CSV file representation of the outputs of the script. You just have to pipe the results to the Export-CSV cmdlet and pass a filename as a parameter

Get-Process | Where-Object {$_.PrivateMemorySize -gt 50MB} | SELECT ProcessName, PrivateMemorySize, CPU | Export-CSV c:\process.csv 
powershell command

If you don't like to see the .NET object type in the first line, simply add the -notype parameter in the Export-CSV cmdlet. While others may say that CSV files are a thing of the past, you can easily save them as Excel files and generate graphs and charts which most management prefer. You can also output the results in HTML format by using the ConvertTo-HTML cmdlet. Like the Export-CSV cmdlet, it would make sense to output the results to an HTML file and pass the filename as a parameter in the Out-File cmdlet

Get-Process | Where-Object {$_.PrivateMemorySize -gt 50MB} | SELECT ProcessName, PrivateMemorySize, CPU | ConvertTo-HTML | Out-File C:\process.htm 
powershell command
Next Steps

You've managed to build a complex script in just a single line in Windows PowerShell using the pipeline, comparison operators and formatting the results.

  • Download and install Windows PowerShell
  • Check out the Windows PowerShell Owner's Manual from Microsoft TechNet
  • Explore the other ways you can use Windows PowerShell in managing your Windows infrastructure

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, December 11, 2018 - 9:36:35 AM - MILADI Back To Top (78449)


Je suis debutant en powershell, je cherche à faire un audit sur tout les instances SQL server.

Instance ,version, BDD, ExtandeProperty, ......

merci pour votre aide .

 voilà le scipte que j'ai trouver sur le net .

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null




#$DiscoveredInstanceList = sqlcmd -L

# or

$DiscoveredInstanceTable = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

$DiscoveredInstanceList = @()

ForEach ($InstanceName in $DiscoveredInstanceTable) {

    $s = $InstanceName.ServerName

    if (-not ([string]::IsNullOrEmpty($InstanceName.Instancename))) {

        $s += "\" + $InstanceName.Instancename


    $DiscoveredInstanceList += $s


# or




try {

    $AdditionalInstancesListFile = Get-Content "\\\Interfaces\Sources_Audite\AdditionalServers.csv"

    $DiscoveredInstanceList += $AdditionalInstancesListFile


catch {}


$ExcludedHostList = "","(local)"


[int]$global:TotalDatabaseStorage = 0

[int]$global:TotalDatabaseCount = 0


$ListInstances = @()

$ListDatabases = @()

$InstanceErrorList = @()

$DisplayInstanceErrorList = $false


$Debug = $false


$CheckDatabaseDetails = $true

$CheckLastUserAccess = $true

$CheckSystemDatabases = $true

$ExtendedProperty  = $true


$OutGridView = $true

$ExportCSV = $true

$ExportCSVFile = "\\\Interfaces\Sources_Audite\SQLInstancesInventory.csv"


ForEach ($InstanceName in $DiscoveredInstanceList) {

    $InstanceName = $InstanceName.trim()

    if ($InstanceName -eq "") {continue}

    if ($InstanceName -eq "Servers:") {continue}


    # Check excluded instances

    if ($ExcludedHostList -contains $InstanceName) {

        if ($Debug) {

            Write-Host $InstanceName " excluded" -ForegroundColor green





    $Server = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($InstanceName)

    $Server.ConnectionContext.ConnectTimeout = 1


    if (!($Server.ComputerNamePhysicalNetBIOS)) {

        $InstanceErrorList +=  "Error connecting $InstanceName"



    else {


        $Databases = $server.Databases | Where-Object Status -eq "normal" | sort-object ID


        Write-Host $InstanceName "-"$Server.Edition "-" $Server.VersionString "(" $Server.ProductLevel ") -" $Server.collation `


        $InstanceStorage = 0

        $DatabaseCount = 0

        foreach ($Database in $Databases) {

             try {

                 If (($CheckSystemDatabases) -or ($Database.iD -gt 0)) {

                    $InstanceStorage += $Database.size

                    $DatabaseCount += 1

                    if ($CheckDatabaseDetails) {

                        if ($debug) {

                            Write-Host "  " $Database.Name "- Owner" $Database.Owner "- RecoveryModel" $Database.RecoveryModel "- Size" $Database.Size.ToString("N") "MB"



                        if ($CheckLastUserAccess) {

                            $tSQL = "SELECT database_id ,


                                           CASE WHEN max(last_user_seek) > max(last_user_scan) THEN max(last_user_seek)

                                                ELSE max(last_user_scan)

                                           END AS LastUserRead,

                                           max(last_user_update) as LastUserWrite

                                    FROM sys.dm_db_index_usage_stats 


                                    WHERE database_id = " + $Database.ID + "

                                    GROUP BY database_id "



                            $LastUserRead = $Database.ExecuteWithResults($tSQL).Tables[0].LastUserRead

                            $LastUserWrite = $Database.ExecuteWithResults($tSQL).Tables[0].LastUserWrite



                            if (-not ([string]::IsNullOrEmpty($LastUserRead))) {$LastUserRead = $LastUserRead.ToString("yyyy-MM-dd HH:mm:ss")}

                            if (-not ([string]::IsNullOrEmpty($LastUserWrite))) {$LastUserWrite = $LastUserWrite.ToString("yyyy-MM-dd HH:mm:ss")}




                        else {

                            $LastUserRead = ""

                            $LastUserWrite = ""






                        #$LastKnownGood = $($Database.ExecuteWithResults("DBCC DBINFO() WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object -First 1).value


                       <# if ($debug) {

                            Write-Host "  " $Database.Name "- Owner" $Database.Owner "- RecoveryModel" $Database.RecoveryModel "- Size" $Database.Size.ToString("N") "MB"



                        if ($ExtendedProperty) {



                                          p.value AS  ExtendedProperty

                                     FROM sys.extended_properties AS p



                                           DB_NAME()=" + $Database.Name + "  

                                           AND p.minor_id=0

                                           AND p.class=0

                                             ORDER BY

                                           [Name] ASC"


                              $ExtendedProperty1 = $Database.ExecuteWithResults($tSQL2).Tables[0].ExtendedProperty1

                              if (-not ([string]::IsNullOrEmpty($ExtendedProperty1))) {$ExtendedProperty1 += $ExtendedProperty1}




                              else {


                                     $ExtendedProperty1="test else" 


                              #$LastKnownGood1 = $($Database.ExecuteWithResults("DBCC DBINFO() WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object -First 1).value 


                        $ListDatabases += New-Object PSObject -Property @{InstanceName=$;













                                                                          CreateDate=$database.CreateDate.ToString("yyyy-MM-dd HH:mm:ss");

                                                                          LastFullBackupDate=$database.LastBackupDate.ToString("yyyy-MM-dd HH:mm:ss");

                                                                          LastLogBackupDate=$database.LastLogBackupDate.ToString("yyyy-MM-dd HH:mm:ss");









            catch {

                Write-host -ForegroundColor Red $_.Exception.Message



        $global:TotalDatabaseStorage += $InstanceStorage

        $global:TotalDatabaseCount += $DatabaseCount


        if ($Debug) {


            Write-Host $InstanceName ": " $DatabaseCount " Databases ("$InstanceStorage.ToString("N") "MB )" 



        $TFList = $Server.EnumActiveGlobalTraceFlags() | Where-Object Global -EQ 1 | Select-Object TraceFlag

        if (-not ([string]::IsNullOrEmpty($TFList))) {

            $TraceFlags = [string]::Join(",",$TFList.TraceFlag)


        else {$TraceFlags = ""} 


        $ListInstances += New-Object PSObject -Property @{NetName=$Server.NetName;






















if ($OutGridView) {

    $ListInstances | Sort-Object InstanceName | Select-Object NetName, InstanceName,Edition,VersionMajor,Version,ProductLevel,`


                                                             TotalSizeMB,ServiceAccount,LoginMode,DatabaseEngineType,ActiveSessions,TraceFlags |   `



    if ($CheckDatabaseDetails) {

        $ListDatabases | Sort-Object InstanceName,DatabaseName | Select-Object InstanceName,VersionMajor,DatabaseName,ExtendedProperty,CompatibilityLevel,`



                                                                               LastFullBackupDate,LastLogBackupDate,LastKnownGood,LastUserRead,LastUserWrite | `





if ($ExportCSV) {


    $ListInstances | Sort-Object InstanceName | Select-Object NetName, InstanceName,Edition,VersionMajor,Version,ProductLevel,`


                                                             TotalSizeMB,ServiceAccount,LoginMode,DatabaseEngineType,ActiveSessions,TraceFlags |   `

                                                Export-CSV $ExportCSVFile -NoTypeInformation  -Force -Delimiter ";"





    if ($CheckDatabaseDetails) {

        $ListDatabases | Sort-Object InstanceName,DatabaseName | Select-Object InstanceName,VersionMajor,DatabaseName,ExtendedProperty,CompatibilityLevel,`



                                                                               LastFullBackupDate,LastLogBackupDate,LastKnownGood,LastUserRead,LastUserWrite | `

                                                                 Export-CSV $ExportCSVFile -NoTypeInformation -Force -Delimiter ";"





# Display grand total 

if ($global:TotalDatabaseCount -gt 0) {

    write-host ""

    write-host "Grand Total :"

    Write-Host $global:TotalDatabaseCount " Databases ("$global:TotalDatabaseStorage.ToString("N") "MB )" 



if ($DisplayInstanceErrorList) {

    write-host ""

    write-host "Errors :"


*** NOTE *** - Si vous souhaitez inclure le code de SQL Server Management Studio (SSMS) dans votre message, veuillez le copier à partir de SSMS et le coller dans un éditeur de texte tel que Bloc-notes avant de copier le code ci-dessous pour supprimer le code. Formatage SSMS.

Wednesday, January 18, 2012 - 9:03:13 AM - bass_player Back To Top (15693)

Great to hear about that, Mohammed. The good thing about learning PowerShell is that you apply the same concepts when managing SharePoint, Active Directory, SCOM, etc. as this is the direction for server management as far as Microsoft is concerned

Wednesday, January 18, 2012 - 1:20:45 AM - Mohammed Moinudheen Back To Top (15691)

Very good introduction Edwin. I am getting interested in PS now :)

get free sql tips
agree to terms