⚙️ PowerShell for SQL Server DBAs: Top Commands and Automation Scripts You Should Be Using

As a SQL Server DBA, you’ve probably automated a few tasks with SQL Agent or T-SQL, but PowerShell — especially with DBATools.io — takes your DBA game to the next level. Whether you’re managing backups, restoring databases, monitoring performance, or migrating servers, PowerShell makes it repeatable, scriptable, and scalable.

This post breaks down the most useful PowerShell commands for SQL Server DBAs, explains why they matter, and even walks you through building your own automation scripts.

---

💾 1. Backup-DbaDatabase

What it does: Backs up SQL Server databases (full, diff, log)
Why it matters: You can script this across dozens of instances, schedule it, and centralize your backup process.

Backup-DbaDatabase -SqlInstance "sql01" -Database "MyDB" -Path "C:\Backups"

✅ Supports compression, encryption, and cleanup
📌 Docs

♻️ 2. Restore-DbaDatabase

What it does: Restores backups with automatic file detection and mapping
Why it matters: You can restore a full backup chain from a folder with a single command.

Restore-DbaDatabase -SqlInstance "sql02" -Path "C:\Backups" -WithReplace

📌 Docs

🧪 3. Test-DbaLastBackup

What it does: Restores the last backup to verify it’s usable
Why it matters: Regularly validating backups is critical — this automates the sanity check.

Test-DbaLastBackup -SqlInstance "sql01"

📌 Docs

📈 4. Get-DbaDiskSpace

What it does: Reports disk space for all attached drives
Why it matters: Prevent outages by detecting low space for logs or backups.

Invoke-DbaDiagnosticQuery -SqlInstance "sql01"

📌 Docs

🩺 5. Invoke-DbaDiagnosticQuery

What it does: Runs Glenn Berry’s popular DMV health scripts
Why it matters: Quickly identify CPU bottlenecks, missing indexes, and memory pressure.

Invoke-DbaDiagnosticQuery -SqlInstance "sql01"

📌 Docs

🔄 6. Copy-DbaDatabase

What it does: Copies a database from one server to another
Why it matters: Great for test environment refreshes or migrations.

Copy-DbaDatabase -Source sql01 -Destination sql02 -Database "MyDB"

📌 Docs

🧹 7. Find-DbaOrphanedFile

What it does: Finds .mdf or .ldf files not attached to SQL Server
Why it matters: Helps you clean up storage across your SQL Server fleet.

Repair-DbaOrphanUser -SqlInstance "sql01" -Database "MyDB"

📌 Docs

🔧 8. Repair-DbaOrphanUser

What it does: Fixes orphaned SQL users after database restores
Why it matters: Eliminates the “user already exists but login does not” error.

Repair-DbaOrphanUser -SqlInstance "sql01" -Database "MyDB"

📌 Docs

🔐 9. Export-DbaLogin & Import-DbaLogin

What they do: Transfers logins between servers with SID, password, and permissions intact
Why they matter: Avoid manual login mapping during migrations.

Export-DbaLogin -SqlInstance "sql01" -FilePath "C:\logins.sql"
Import-DbaLogin -SqlInstance "sql02" -Path "C:\logins.sql"

📌 Docs

💼 10. Start-DbaMigration

What it does: Migrate entire SQL instances: logins, jobs, databases, etc.
Why it matters: One of the most powerful and complete DBA tools for moving environments.

Start-DbaMigration -Source sql01 -Destination sql02

📌 Docs

🧰 Build Your Own DBA Automation Script

PowerShell isn’t just about running one-off commands — it’s about building smart, repeatable solutions.

Example: Daily Health Check Automation

$instance = "sql01"
$date = Get-Date -Format yyyy-MM-dd

# Step 1: Check disk space
Get-DbaDiskSpace -SqlInstance $instance |
    Export-Csv "C:\Reports\DiskSpace_$date.csv" -NoTypeInformation

# Step 2: Run diagnostic queries
Invoke-DbaDiagnosticQuery -SqlInstance $instance |
    Export-Csv "C:\Reports\Diag_$date.csv" -NoTypeInformation

# Step 3: Email summary (optional)
Send-MailMessage -From "sqlalerts@yourdomain.com" -To "dba@yourdomain.com" `
    -Subject "Daily SQL Health Report - $date" `
    -Body "Attached are the daily diagnostics and disk space reports." `
    -Attachments "C:\Reports\DiskSpace_$date.csv", "C:\Reports\Diag_$date.csv" `
    -SmtpServer "smtp.yourdomain.com"

✅ Schedule this via Windows Task Scheduler or a SQL Agent PowerShell job
📌 Combine with logging, alerting, and Slack/Teams integration if needed

🌐 Why DBATools.io Is a Must-Have

• Open source and community-driven
• Over 700 commands purpose-built for SQL Server
• Continuously updated with real-world fixes and edge-case handling
• Great documentation and examples: https://dbatools.io

🧠 Final Thoughts

PowerShell with DBATools is a game-changer for SQL Server DBAs. Whether you manage 2 servers or 200, learning these commands can save hours of manual work, reduce mistakes, and let you sleep better at night.

Even if you start with just one or two scripts — like Test-DbaLastBackup or Repair-DbaOrphanUser — you’re already ahead of the curve.