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"
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.
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
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'
)
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)
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"
File Paths
$BackupRoot = 'D:Backups'
$ServerName = 'SQL-PROD-01'
$DateStamp = Get-Date -Format 'yyyyMMdd'
$BackupPath = "$BackupRoot$ServerName$DateStamp"
$LogFile = "$BackupRootLogsBackupCheck_$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 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
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.
The post PowerShell Newbie – Variables appeared first on GarryBargsley.com.
The post PowerShell Newbie – Variables appeared first on SQLServerCentral.
Log in
