powershell splatting

Splatting in PowerShell makes code easier to read. Instead of typing a bunch of parameters allllll across the screen, you can use an easy-to-read hashtable or array. Argument splatting was introduced in PowerShell v3 and works with all PowerShell commands, not just dbatools.

Note: I’ve only used splatting with hashtables, as they allow me to be explicit about which parameters I’m passing. It appears that arrays would employ positional parameters, which is less wordy but leaves room for error.

Example 1

In the example below, sql2008 will be migrated to sql2017 using the backup restore method.

# Ultimately, this (which scrolls offscreen):
Start-DbaMigration -Source sql2008 -Destination sql2017 -BackupRestore -SharedPath \\nas\sql\migration -Force | Select * | Out-GridView

# becomes this:
$splat = @{
    Source = "sql2008"
    Destination = "sql2017"
    BackupRestore = $true
    SharedPath = "\\nas\sql\migration"
    Exclude = "AgentServer","Audits","BackupDevices"

Start-DbaMigration @splat -Force | Select * | Out-GridView

Note that the dollar sign in the variable name $splat becomes an at sign, @splat when executing the command. Also, not every parameter has to belong to the splat. As you can see, -Force is specified separately.

Also, the use of quotes is slightly different. While quotes are sometimes optional (string variables without special characters) when executing a command the traditional way, quotes around string values are required in a hashtable.

Example 2

The first example was a short intro and the next ones are a bit longer. Imagine the following splat used with one of our larger commands, Invoke-DbaDbLogShipping.

 $params = @{
    Source = "localhost\sql2016"
    Destination = "localhost\sql2017"
    Database = "shipped"
    BackupNetworkPath= "\\localhost\backups"
    PrimaryMonitorServer = "localhost\sql2017"
    SecondaryMonitorServer = "localhost\sql2017"
    BackupScheduleFrequencyType = "Daily"
    BackupScheduleFrequencyInterval = 1
    CompressBackup = $true
    CopyScheduleFrequencyType = "Daily"
    CopyScheduleFrequencyInterval = 1
    GenerateFullBackup = $true
    Force = $true

Invoke-DbaDbLogShipping @params

You can see above that switches such as -Force or -CompressBackup when used in a splat will use the following syntax Force = $true.

Example 3

Here’s another example we use in our Extended Events presentation.

$params = @{
    SmtpServer = "localhost"
    To = "[email protected]"
    Sender = "[email protected]"
    Subject = "Deadlock Captured"
    Body = "Caught a deadlock"
    Event = "xml_deadlock_report"
    Attachment = "xml_report"
    AttachmentFileName = "report.xdl"

$emailresponse = New-DbaXESmartEmail @params
Start-DbaXESmartTarget -SqlInstance sever01\sql2017 -Session "Deadlock Graphs" -Responder $emailresponse

Example 4

And finally, an example that will be included in the High Availability blog post when it finally comes out.

$cred = Get-Credential sqladmin
$params = @{
    Primary = "sql1"
    PrimarySqlCredential = $cred
    Secondary = "sql2", "sql3"
    SecondarySqlCredential = $cred
    Name = "test-ag"
    Database = "pubs"
    ClusterType = "None"
    SeedingMode = "Automatic"
    FailoverMode = "Manual"
    Confirm = $false
New-DbaAvailabilityGroup @params

VS Code

In his blog post Easily Splatting PowerShell with VS Code, Rob Sewell shows how VS Code can easily create splats, as explained in his instructions and video below. First, run:

Import-Module -Name EditorServicesCommandSuite
Import-EditorCommand -Module EditorServicesCommandSuite


Write the command, leave the cursor on a parameter, hit F1 – Choose PowerShell : Show Additional Commands (or use a keyboard shortcut) type splat press enter. Done 🙂

Rob also tweeted to “be careful where your cursor is. I usually put it in the command. Sometimes it goes screwy if the cursor is at the EoL.”

Have fun

If you use commands with a number of parameters or even want to reuse parameter sets, splatting is an ideal solution.

- Chrissy

8 thoughts on “powershell splatting

  1. Pingback: Splatting In Powershell – Curated SQL

  2. Simon Reply

    I’ve always used the backtick symbol ( ` )

    I can see the benefit of splatting, will likely adopt. Have you used backtick before? Have an opinion on it?


    • Chrissy LeMaire Post authorReply

      I have used it and even know a bit of trivia about it! The backtick works because it escapes the next character which is a line break 🧙‍♂️ But I don’t like the way it looks so I’ve only used it a couple times.

  3. Micha Piatek Reply

    I just found this site the other day reading a blog post by Kendra Little and I am working with dbatools only since this week Great article, great tools. I am amazed and excited. Great job. This is the PS on SQL know how library I have been looking for.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.