PowerShell Strikes Back: The Empire of Variables

This is Week 2 of PowerShell Strikes Back – a four-week May series for SQL Server DBAs who have dabbled in PowerShell but never stopped to nail down the fundamentals. If you missed Week 1 on single vs double quotes, start there first – it’ll make this post land better.

Last week, we learned that quotes are not created equal. This week, we’re going deeper into the building blocks that make PowerShell scripts actually useful: variables.

If quotes are your lightsaber, variables are the Force itself. They carry information from one part of your script to another. They make the difference between a script that works on one server and a script that works on all of them. Get comfortable with variables, and you’ll look back at your pre-PowerShell DBA life the way Luke looked back at Tatooine – relieved to be moving on.

The Basics – Declaring and Assigning Variables

In PowerShell, every variable starts with a dollar sign ($). You don’t need to declare a variable before using it – you just assign it a value and PowerShell figures out the rest.

# Assigning basic variables
$ServerName   = 'SQL-PROD-01'
$DatabaseName = 'WideWorldImporters'
$MaxConnections = 100
$IsProduction   = $true

Write-Host "Server: $ServerName"
Write-Host "Database: $DatabaseName"
Write-Host "Max Connections: $MaxConnections"
Write-Host "Production: $IsProduction"

# Assigning basic variables

$ServerName = 'SQL-PROD-01'

$DatabaseName = 'WideWorldImporters'

$MaxConnections = 100

$IsProduction = $true

Write-Host "Server: $ServerName"

Write-Host "Database: $DatabaseName"

Write-Host "Max Connections: $MaxConnections"

Write-Host "Production: $IsProduction"

Notice there are no data type declarations here. PowerShell infers the type from what you assign, strings get quotes, integers don’t, and booleans use the built-in $true and $false values. For most DBA scripting, this works perfectly fine.

Why Loose Typing Can Strike Back

PowerShell’s automatic type inference is convenient until it isn’t. Consider this:

$Port = '1433'
$NewPort = $Port + 1
Write-Host $NewPort
# Output: 14331
# Not 1434. PowerShell treated $Port as a string and concatenated instead of adding.

$Port = '1433'

$NewPort = $Port + 1

Write-Host $NewPort

# Output: 14331

# Not 1434. PowerShell treated $Port as a string and concatenated instead of adding.

The Empire strikes back through your own assumptions. If you need a variable to behave as a specific type, declare it explicitly by putting the type in brackets before the variable name:

[int]$Port    = '1433'    # Now it's an integer
[string]$SPID = 57        # Now it's a string
[bool]$IsAG   = $true     # Explicitly boolean
[datetime]$BackupTime = '2026-05-04 02:00:00'

$NewPort = $Port + 1
Write-Host $NewPort
# Output: 1434 — the way the Force intended

[int]$Port = '1433' # Now it's an integer

[string]$SPID = 57 # Now it's a string

[bool]$IsAG = $true # Explicitly boolean

[datetime]$BackupTime = '2026-05-04 02:00:00'

$NewPort = $Port + 1

Write-Host $NewPort

# Output: 1434 — the way the Force intended

As a DBA, you’ll care about this most when dealing with port numbers, SPIDs, database IDs, and any value that comes back from a query as a string but needs to be treated as a number.

Variables That DBAs Actually Use

Let’s get practical. Here are the kinds of variables you’ll build real scripts around.

Server and Instance Lists

# Single server
$TargetServer = 'SQL-PROD-01'

# Array of servers — we'll loop over these in Week 3
$SqlServers = @(
    'SQL-PROD-01',
    'SQL-PROD-02',
    'SQL-DR-01'
)

# Single server

$TargetServer = 'SQL-PROD-01'

# Array of servers — we'll loop over these in Week 3

$SqlServers = @(

'SQL-PROD-01',

'SQL-PROD-02',

'SQL-DR-01'

)

Credentials

# Prompt for credentials securely — never hardcode passwords
$Credential = Get-Credential

# Or build a credential object from a stored secure string
$SecurePass  = ConvertTo-SecureString 'YourPassword' -AsPlainText -Force
$Credential  = New-Object System.Management.Automation.PSCredential('sa', $SecurePass)

# Prompt for credentials securely — never hardcode passwords

$Credential = Get-Credential

# Or build a credential object from a stored secure string

$SecurePass = ConvertTo-SecureString 'YourPassword' -AsPlainText -Force

$Credential = New-Object System.Management.Automation.PSCredential('sa', $SecurePass)

Date and Time Variables

# Useful for backup paths, log file names, report timestamps
$Today        = Get-Date
$DateStamp    = Get-Date -Format 'yyyyMMdd'
$TimeStamp    = Get-Date -Format 'yyyyMMdd_HHmmss'
$BackupWindow = (Get-Date).AddHours(-24)  # 24 hours ago

Write-Host "Running backup check for window: $BackupWindow to $Today"

# Useful for backup paths, log file names, report timestamps

$Today = Get-Date

$DateStamp = Get-Date -Format 'yyyyMMdd'

$TimeStamp = Get-Date -Format 'yyyyMMdd_HHmmss'

$BackupWindow = (Get-Date).AddHours(-24) # 24 hours ago

Write-Host "Running backup check for window: $BackupWindow to $Today"

File Paths

$BackupRoot  = 'D:\Backups'
$ServerName  = 'SQL-PROD-01'
$DateStamp   = Get-Date -Format 'yyyyMMdd'
$BackupPath  = "$BackupRoot\$ServerName\$DateStamp"
$LogFile     = "$BackupRoot\Logs\BackupCheck_$DateStamp.log"

Write-Host "Backup path: $BackupPath"
Write-Host "Log file: $LogFile"

$BackupRoot = 'D:\Backups'

$ServerName = 'SQL-PROD-01'

$DateStamp = Get-Date -Format 'yyyyMMdd'

$BackupPath = "$BackupRoot\$ServerName\$DateStamp"

$LogFile = "$BackupRoot\Logs\BackupCheck_$DateStamp.log"

Write-Host "Backup path: $BackupPath"

Write-Host "Log file: $LogFile"

Storing Query Results in Variables

This is where variables go from useful to genuinely powerful for DBA work. You can store the results of a SQL query or a dbatools command directly in a variable and work with the data in PowerShell.

# Store dbatools query results in a variable
$DatabaseList = Get-DbaDatabase -SqlInstance 'SQL-PROD-01' |
    Where-Object { $_.IsSystemObject -eq $false }

# Now $DatabaseList holds all user databases — inspect it
$DatabaseList.Count                    # How many databases
$DatabaseList[0].Name                  # Name of the first one
$DatabaseList | Select-Object Name, Size, RecoveryModel  # Specific properties

# Store dbatools query results in a variable

$DatabaseList = Get-DbaDatabase -SqlInstance 'SQL-PROD-01' |

Where-Object { $_.IsSystemObject -eq $false }

# Now $DatabaseList holds all user databases — inspect it

$DatabaseList.Count # How many databases

$DatabaseList[0].Name # Name of the first one

$DatabaseList | Select-Object Name, Size, RecoveryModel # Specific properties

# Store raw T-SQL results using Invoke-DbaQuery
$Results = Invoke-DbaQuery -SqlInstance 'SQL-PROD-01' -Query @"
    SELECT 
        name,
        state_desc,
        recovery_model_desc
    FROM sys.databases
    WHERE database_id &gt; 4
"@

# Work with the results
foreach ($Row in $Results) {
    Write-Host "$($Row.name) is $($Row.state_desc) — Recovery: $($Row.recovery_model_desc)"
}
# We'll cover foreach properly next week — consider this a teaser

# Store raw T-SQL results using Invoke-DbaQuery

$Results = Invoke-DbaQuery -SqlInstance 'SQL-PROD-01' -Query @"

SELECT

name,

state_desc,

recovery_model_desc

FROM sys.databases

WHERE database_id > 4

# Work with the results

foreach ($Row in $Results) {

Write-Host "$($Row.name) is $($Row.state_desc) — Recovery: $($Row.recovery_model_desc)"

}

# We'll cover foreach properly next week — consider this a teaser

Variable Scope — Know Your Territory

Variables in PowerShell have a scope; they exist in a specific context and may not be visible outside it. For most DBA scripts running top to bottom in a single file, this won’t trip you up. But once you start writing functions or calling scripts from other scripts, scope matters.

# Script scope — visible throughout the script
$script:ServerName = 'SQL-PROD-01'

# Global scope — visible everywhere, including child scripts
$global:LogPath = 'D:\Logs'

# Local scope — the default, visible only in the current block
$ServerName = 'SQL-PROD-01'  # Local by default

# Script scope — visible throughout the script

$script:ServerName = 'SQL-PROD-01'

# Global scope — visible everywhere, including child scripts

$global:LogPath = 'D:\Logs'

# Local scope — the default, visible only in the current block

$ServerName = 'SQL-PROD-01' # Local by default

The Jedi rule of thumb: default local scope is fine for most scripts. Only reach for $script: or $global: When you have a specific reason, unnecessary global variables are the midi-chlorians of PowerShell. Everyone argues about them, and they usually cause more problems than they solve.

A Few Variables You Get for Free

PowerShell has a set of automatic variables that are always available and genuinely useful for DBA scripting:

Variable	What It Contains	DBA Use Case
`$_` or `$PSItem`	Current pipeline object	Used inside loops and Where-Object filters
`$true` / `$false`	Boolean values	Flag variables, conditional logic
`$null`	Empty/no value	Checking if a result came back empty
`$Error`	An array of recent errors	Checking what went wrong after a failure
`$MyInvocation`	Info about the current script	Getting the script name for log entries
`$PSScriptRoot`	The directory the script lives in	Building relative paths from the script location

Your Assignment, Rebel Pilot

Open a PowerShell window and create typed variables for a server name (string), a port number (int), and a backup date (datetime).
Build a backup file path string using double quotes that combines all three into a meaningful path.
If you have dbatools available, run Get-DbaDatabase against a local or dev instance and store the results in a variable. Then check $Results.Count to see how many databases came back.

Variables are the backbone of every script you’ll write. Get comfortable assigning, typing, and reading them. Next week, we put them to work inside loops.

Next week in PowerShell Strikes Back: Return of the Loop foreach, ForEach-Object, and how to stop running the same command twelve times by hand. See you on May 18th.

2 Replies to “PowerShell Strikes Back: The Empire of Variables5 min read”

Pingback: Working with Variables in Powershell – Curated SQL
Robert Sterbal says:

May 15, 2026 at 11:51 pm

Is there a good resource for handling credential in more detail?

How do I pass credentials to a website that requires them?

Thanks!

PowerShell Strikes Back: The Empire of Variables5 min read