PowerShell Strikes Back: Know Your Quotes, Young Padawan

This is Week 1 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. New posts every Monday in May. May the 4th be with you.

A long time ago, in a query window far, far away, a DBA typed their first PowerShell command. It looked simple enough. A variable, a string, a path to a backup folder. But then something went wrong, the variable didn’t expand, the connection string came out mangled, the script ran without errors, and produced completely wrong output. They had no idea why.

Nine times out of ten, the culprit was quotes.

Welcome to the first week of PowerShell Strikes Back. Before we wield any real power, we need to master one of the most fundamental yet misunderstood concepts in PowerShell: the difference between single and double quotes. Think of this as your first lesson at the Jedi Academy. It looks simple. But get it wrong, and your scripts will betray you at the worst possible moment.

The Two Sides of the Force

In PowerShell, both single quotes (') and double quotes (") define strings. On the surface, they look interchangeable. They are not. The difference comes down to one word: expansion.

• Single quotes — what you type is exactly what you get. No interpretation. No expansion. PowerShell doesn’t look inside. It’s the light side: calm, literal, predictable.
• Double quotes — PowerShell looks inside the string and expands anything it recognizes, like variables and special characters. More powerful, but only if you know what you’re doing.

Let’s see that in action right away.

# Single quotes — literal, no expansion
$ServerName = 'SQL-PROD-01'
Write-Host 'The server is $ServerName'
# Output: The server is $ServerName

# Double quotes — variable gets expanded
Write-Host "The server is $ServerName"
# Output: The server is SQL-PROD-01

That’s the core rule. Single quotes take everything literally. Double quotes read the room.

Why This Matters for a DBA

As a SQL Server DBA, you’re constantly building connection strings, file paths, SQL queries, and log messages. Getting quotes wrong in any of these can silently break your script, and that’s the dangerous part. It won’t always throw an error. It’ll just produce output that’s quietly, completely wrong. Let’s look at some real DBA scenarios.

Building a Connection String

$ServerName = 'SQL-PROD-01'
$Database   = 'WideWorldImporters'

# Wrong — single quotes won't expand your variables
$ConnString = 'Server=$ServerName;Database=$Database;Integrated Security=True'
Write-Host $ConnString
# Output: Server=$ServerName;Database=$Database;Integrated Security=True
# This connection string will never work. The Force is not strong with this one.

# Right — double quotes expand the variables
$ConnString = "Server=$ServerName;Database=$Database;Integrated Security=True"
Write-Host $ConnString
# Output: Server=SQL-PROD-01;Database=WideWorldImporters;Integrated Security=True

Building a Backup File Path

$ServerName = 'SQL-PROD-01'
$BackupDate = Get-Date -Format 'yyyyMMdd'
$BackupPath = "D:Backups$ServerName$BackupDate"

Write-Host $BackupPath
# Output: D:BackupsSQL-PROD-0120260504

Notice the date format inside Get-Date uses single quotes because you want yyyyMMdd treated as a literal format string, not expanded. That’s a perfect example of using each type for the right job. Even in a single line, you can use both.

The Escape Sequence – When You Need Both

Sometimes you need a double quote inside a double-quoted string, or a single quote inside a single-quoted string. PowerShell handles this with the backtick (`) as an escape character inside double quotes, or by doubling the quote character inside single-quoted strings.

# Backtick to escape a double quote inside a double-quoted string
$Message = "The database name is `"WideWorldImporters`""
Write-Host $Message
# Output: The database name is "WideWorldImporters"

# Doubling a single quote inside a single-quoted string
$Message = 'It''s a SQL Server DBA''s life'
Write-Host $Message
# Output: It's a SQL Server DBA's life

The backtick is PowerShell’s lightsaber – precise, powerful, and a little awkward until it becomes second nature.

Here Strings – The Jedi Master Move for T-SQL

If you’ve ever tried to embed a multi-line T-SQL query inside a PowerShell string, you’ve probably ended up in quote hell – escaping single quotes inside SQL strings, fighting with line breaks, questioning your life choices. Here strings are the answer.

A here string starts with @" or @' on its own line and ends with "@ or '@ on its own line. Everything inside is treated as a raw string – no escaping, no surprises.

# Here string with double quotes — variables still expand inside
$DatabaseName = 'WideWorldImporters'

$Query = @"
SELECT 
    name,
    state_desc,
    recovery_model_desc
FROM sys.databases
WHERE name = '$DatabaseName'
ORDER BY name;
"@

Write-Host $Query

Inside the here string, you can use single quotes freely in SQL syntax without any escaping. This is the cleanest way to embed T-SQL in a PowerShell script — and every DBA should have this in their toolkit from day one.

Quick Reference

Your Assignment, Padawan

1. Create a variable called $InstanceName and assign it one of your SQL Server instance names.
2. Write a Write-Host using single quotes referencing the variable – observe that it doesn’t expand.
3. Switch to double quotes and watch it work correctly.
4. Build a here string containing a simple SELECT @@VERSION query and print it to the console.

It sounds trivial. Do it anyway. Muscle memory with quotes will save you hours of head-scratching down the road. The Jedi Masters didn’t skip the basics either.

Next week in PowerShell Strikes Back: The Empire Strikes Back – we go deep on variables. How to declare them, type them, scope them, and put them to work in real DBA scripts. See you on May 11th.

May the 4th be with you.

The post PowerShell Strikes Back: Know Your Quotes, Young Padawan appeared first on GarryBargsley.com.