How to Create Reusable PowerShell Functions for Microsoft SQL Server Database Administration

In my last blog article on Getting Started with Administering and Managing Microsoft SQL Server with PowerShell, I left off by introducing you to how to query information about your SQL Server using PowerShell with SQL Server Management Objects (SMO).

In this blog article, I'll pick up where I left off and write a reusable PowerShell function to return information about SQL Server files and file groups while making the use of SMO transparent to the user of the function.

I'm going to start off by importing the SQL Server 2014 PowerShell module that's installed on the Windows 8.1 workstation that's being used in this demonstration. For specifics about this, see the previous blog article that was referenced.

1Import-Module -Name SQLPS -DisableNameChecking

Create a new SMO Object in PowerShell and store it in a variable:

1$SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList 'SQL01'

It looks like the information can be retrieved like this:

1$sql.databases |
2Select-Object -First 1 -Property Name,
3                                 @{label='FileGroups';expression={$_.filegroups}},
4                                 @{label='Files';expression={$_.filegroups.files}}

sql-050614-1a.jpg

The problem with retrieving it that way is it doesn't really scale since when more than one item is returned, you end up with a collection of items. So what you need to do is to iterate through each item that could be a collections of items (databases, filegroups, and files), create a custom object, and then return that object:

 1Import-Module -Name SQLPS -DisableNameChecking
 2$SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $ComputerName
 3
 4foreach ($database in $SQL.Databases) {
 5
 6    foreach ($filegroup in $database.filegroups) {
 7
 8        foreach ($file in $filegroup.files) {
 9
10            [PSCustomObject]@{
11                DatabaseName = $database.Name
12                FileGroup = $filegroup
13                Name = $file.Name
14                PrimaryFile = $file.IsPrimaryFile
15            }
16
17        }
18    }
19}

sql-050614-2.jpg

That looks better, but it's very static. It would need to be modified manually each time for a different database server name or SQL instance and do you really want a junior level DBA or System Administrator that you might delegate this task to, to be manually modifying the PowerShell code? I didn't think so. That's why this function needs to be turned into a parameterized function so the values such as ComputerName (ServerName) can be specified dynamically on the fly with no modification to the code whatsoever:

 1function Get-MrDbFileInfo {
 2
 3    [CmdletBinding()]
 4    param (
 5        [Parameter(Mandatory)]
 6        [Alias('ServerName')]
 7        [string]$ComputerName
 8    )
 9
10    Import-Module -Name SQLPS -DisableNameChecking
11    $SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $ComputerName
12
13    foreach ($database in $SQL.Databases) {
14
15        foreach ($filegroup in $database.filegroups) {
16
17            foreach ($file in $filegroup.files) {
18
19                [PSCustomObject]@{
20                    DatabaseName = $database.Name
21                    FileGroup = $filegroup
22                    Name = $file.Name
23                    PrimaryFile = $file.IsPrimaryFile
24                }
25
26            }
27        }
28    }
29}

sql-050614-3.jpg

That's a little better. The function is named Get-MrDbFileInfo and it's been saved in a ps1 file named Get-MrDbFileInfo.ps1. I used my initials (Mr for Mike Robbins) to help prevent name collisions with other functions that someone else may have created that could have the same name (Get-DbFileInfo).

In the previous example, the function was dot sourced and then run against a database server running SQL Server 2014 named SQL01, it was then run against another database server running SQL Server 2008 R2 named SQL02.

The problem now is what if you wanted to run it against a named instance instead of the default one and maybe you're only looking for information about a specific database?

 1function Get-MrDbFileInfo {
 2
 3    [CmdletBinding()]
 4    param (
 5        [Parameter(Mandatory)]
 6        [Alias('ServerName')]
 7        [string]$ComputerName,
 8
 9        [ValidateNotNullOrEmpty()]
10        [string]$InstanceName = 'Default',
11
12        [ValidateNotNullOrEmpty()]
13        [string[]]$DatabaseName = '*'
14    )
15
16    Import-Module -Name SQLPS -DisableNameChecking
17
18    if ($InstanceName -eq 'Default') {
19                $SQLInstance = $ComputerName
20    }
21    else {
22        $SQLInstance = "$ComputerName\$InstanceName"
23    }
24
25    $SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $SQLInstance
26    $databases = $SQL.Databases | Where-Object Name -like "$DatabaseName"
27
28    foreach ($database in $databases) {
29
30        foreach ($filegroup in $database.filegroups) {
31
32            foreach ($file in $filegroup.files) {
33
34                [PSCustomObject]@{
35                    DatabaseName = $database.Name
36                    FileGroup = $filegroup
37                    Name = $file.Name
38                    PrimaryFile = $file.IsPrimaryFile
39                }
40            }
41        }
42    }
43}

sql-050614-4.jpg

With all this additional functionality that's being added to this function, it's becoming a versatile, reusable tool to query file and filegroup related information from your SQL servers. No need for the user of it to make any manual changes if they want to query information for a specific database server, named instance of SQL, specific database, multiple databases, or to use wildcards in the database name to return information for multiple databases with similar names without having to manually specify each one.

More polish is still needed though. Being able to accept pipeline input on the database name parameter would be nice so the output of one command that returns database names can be piped into this function. Error handling and comment based help are needed as well.

  1#Requires -Version 3.0
  2function Get-MrDbFileInfo {
  3
  4<#
  5.SYNOPSIS
  6    Returns database file information for a Microsoft SQL Server database.
  7
  8.DESCRIPTION
  9    Get-MrDbFileInfo is a function that returns the database file information
 10    for one or more Microsoft SQL Server databases.
 11
 12.PARAMETER ComputerName
 13    The computer that is running Microsoft SQL Server that your targeting to
 14    query database file information on.
 15
 16.PARAMETER InstanceName
 17    The instance name of SQL Server to return database file informmation for.
 18    The default is the default SQL Server instance.
 19
 20.PARAMETER DatabaseName
 21    The database(s) to return file informmation for. The default is all databases.
 22
 23.EXAMPLE
 24    Get-MrDbFileInfo -ComputerName sql01
 25
 26.EXAMPLE
 27     Get-MrDbFileInfo -ComputerName sql01 -DatabaseName master, msdb, model
 28
 29.EXAMPLE
 30     Get-MrDbFileInfo -ComputerName sql01 -InstanceName MrSQL -DatabaseName master,
 31     msdb, model
 32
 33.EXAMPLE
 34    'master', 'msdb', 'model' | Get-MrDbFileInfo -ComputerName sql01
 35
 36.INPUTS
 37    String
 38
 39.OUTPUTS
 40    PSCustomObject
 41
 42.NOTES
 43    Author:  Mike F Robbins
 44    Website: http://mikefrobbins.com
 45    Twitter: @mikefrobbins
 46#>
 47
 48    [CmdletBinding()]
 49    param (
 50        [Parameter(Mandatory)]
 51        [Alias('ServerName')]
 52        [string]$ComputerName,
 53
 54        [ValidateNotNullOrEmpty()]
 55        [string]$InstanceName = 'Default',
 56
 57        [Parameter(ValueFromPipeline)]
 58        [ValidateNotNullOrEmpty()]
 59        [string[]]$DatabaseName = '*'
 60    )
 61
 62    BEGIN {
 63        $problem = $false
 64
 65        if (-not (Get-Module -Name SQLPS)) {
 66            try {
 67                Import-Module -Name SQLPS -DisableNameChecking -ErrorAction Stop
 68            }
 69            catch {
 70                $problem = $true
 71                Write-Warning -Message "An error has occured.  Error details: $_.Exception.Message"
 72            }
 73        }
 74
 75        if (-not ($problem)) {
 76            if ($InstanceName -eq 'Default') {
 77                $SQLInstance = $ComputerName
 78            }
 79            else {
 80                $SQLInstance = "$ComputerName\$InstanceName"
 81            }
 82
 83            $SQL = New-Object('Microsoft.SqlServer.Management.Smo.Server') -ArgumentList $SQLInstance
 84        }
 85    }
 86
 87    PROCESS {
 88
 89        try {
 90            $databases = $SQL.Databases | Where-Object Name -like "$DatabaseName"
 91        }
 92        catch {
 93            $problem = $true
 94            Write-Warning -Message "An error has occured.  Error details: $_.Exception.Message"
 95        }
 96
 97        if (-not $problem) {
 98            foreach ($database in $databases) {
 99                Write-Verbose -Message "Retrieving information for database: $database."
100
101                foreach ($filegroup in $database.filegroups) {
102                    Write-Verbose -Message "Retrieving information for filegroup: $filegroup."
103
104                    foreach ($file in $filegroup.files) {
105                        Write-Verbose -Message "Retrieving information for file: $file."
106
107                        [PSCustomObject]@{
108                            ComputerName = $SQL.Information.ComputerNamePhysicalNetBIOS
109                            InstanceName = $(if ($SQL.InstanceName){$SQL.InstanceName} else{'Default'})
110                            DatabaseName = $database.Name
111                            FileGroup = $filegroup
112                            Name = $file.Name
113                            FileName = $file.FileName
114                            DefaultPath = $(if (($file.filename -replace '[^\\]+$') -eq ($SQL.DefaultFile)){'True'} else{'False'})
115                            PrimaryFile = $file.IsPrimaryFile
116                            'Size(MB)' = '{0:N2}' -f ($file.Size / 1KB)
117                            'FreeSpace(MB)' = '{0:N2}' -f ($file.AvailableSpace / 1KB)
118                            MaxSize = $file.MaxSize
119                            "Growth($($file.GrowthType))" = $file.Growth
120                            'VolumeFreeSpace(GB)' = '{0:N2}' -f ($file.VolumeFreeSpace / 1MB)
121                            NumberOfDiskReads = $file.NumberOfDiskReads
122                            'ReadFromDisk(MB)' = '{0:N2}' -f ($file.BytesReadFromDisk / 1MB)
123                            NumberOfDiskWrites = $file.NumberOfDiskWrites
124                            'WrittenToDisk(MB)' = '{0:N2}' -f ($file.BytesWrittenToDisk / 1MB)
125                            ID = $file.ID
126                            Offline = $file.IsOffline
127                            ReadOnly = $file.IsReadOnly
128                            ReadOnlyMedia = $file.IsReadOnlyMedia
129                            Sparse = $file.IsSparse
130                            DesignMode = $file.IsDesignMode
131                            Parent = $file.Parent
132                            State = $file.State
133                            UsedSpace = $file.UsedSpace
134                            UserData = $file.UserData
135                        }
136                    }
137                }
138            }
139        }
140    }
141}

sql-050614-5.jpg

As you can see, the error handling is working as I forgot which database server my MrSharePoint SQL instance was installed on. I was also able to pipe the database name in and not only pipe it in, but pipe it in with a wildcard.

The problem now is that too may properties have been added and I only want certain properties returned by default in the table and list views:

sql-050614-6a.jpg

I also don't want to have to dot source the function every time I want to use it. I'll resolve these issues in my next blog article by creating a module, module manifest, and custom formatting via a .ps1xml file.

µ