dbatools: SQL Server Administration Made Practical

Jul 4, 2025
4 min read

Managing SQL Server day-to-day isn't just about setting it up, it’s about keeping it running smoothly: managing logins, scheduling jobs, copying configurations, and troubleshooting when things fall out of line. That’s where dbatools really proves its worth beyond initial setup.

This post focuses on the administration side of dbatools, the tasks you come back to repeatedly. Whether you're migrating logins, checking agent jobs, or replicating objects across servers, there’s probably a command for it. And if there isn’t, well, you can help build one—because it’s a free, community-driven tool you can actually contribute to.

Also worth mentioning: most dbatools commands support multiple servers at once. So if you're managing a farm of instances, you don’t have to loop through them manually—just pass them all in. It makes multi-server administration feel a lot less like a juggling act.

Logins: Copy, Migrate, Update, Remove

Logins are a common pain point, especially during migrations. dbatools makes it easier by keeping SIDs consistent (or regenerating them if you prefer), handling passwords, and applying changes across multiple instances.

Copy-DbaLogin

Clone a login from one server to another. Great for migrations or syncing environments.

Copy-DbaLogin -Source SQL-DB1 -Destination SQL-DB6 -Login app_test -ExcludeSystemLogins -Force -KillActiveConnection

Export-DbaLogin

Export a login to a .sql script file so you can store or audit it later.

Export-DbaLogin -SqlInstance SQL-DB1 -Login app_test -Path C:\Temp\Logins\app_test.sql

Get-DbaLogin

List existing logins. You can filter by login type.

Get-DbaLogin -SqlInstance SQL-DB1 -Type SQL

New-DbaLogin

Create logins on one or more servers in one go.

New-DbaLogin -SqlInstance SQL-DB1,SQL-DB2 -Login Newlogin

Remove-DbaLogin

Drop logins from one or more instances. Handy for cleaning up after migrations or environment refreshes.

Remove-DbaLogin -SqlInstance SQL-DB1, SQL-DB2, SQL-DB3 -Login app_test

Rename-DbaLogin

Rename a login (including forcing the change if it already exists).

Rename-DbaLogin -SqlInstance SQL-DB1 -Login app_test -NewLogin user_test -Force

Set-DbaLogin

Enable, disable, or change passwords for a login.

Set-DbaLogin -SqlInstance SQL-DB1 -Login user_test -Enable

SQL Agent Jobs: Create, Copy, Disable, and Monitor

Agent jobs are essential for backups, cleanups, alerts—you name it. Managing them across servers can get messy. dbatools makes it easier to script, sync, and monitor jobs without ever opening SSMS.

Find-DbaAgentJob

Search for jobs by name or pattern.

Find-DbaAgentJob -SqlInstance SQL-DB1 -JobName *backup*

Get-DbaAgentJob

List jobs and their properties. Combine with filtering or piping to copy jobs selectively.

Copy-DbaAgentJob

Copy jobs from one instance to another. You can even exclude disabled jobs.

Get-DbaAgentJob -SqlInstance SQL-DB1 -ExcludeDisabledJobs | Where-Object Name -eq 'Job1' | Copy-DbaAgentJob -Destination 'SQL-DB2' -Force

New-DbaAgentJob

Create new jobs with basic properties.

New-DbaAgentJob -SqlInstance SQL-DB1 -Job 'Job1' -Description 'Just another job'

Remove-DbaAgentJob

Delete jobs from multiple servers at once. Just list the instances.

Remove-DbaAgentJob -SqlInstance SQL-DB1, SQL-DB2, SQL-DB3 -Job Job1

Set-DbaAgentJob

Disable or enable existing jobs.

Set-DbaAgentJob -SqlInstance SQL-DB1 -Job Job1 -Disabled

Start-DbaAgentJob

Kick off a job manually.

Start-DbaAgentJob -SqlInstance SQL-DB1 -Job Job1

Stop-DbaAgentJob

Stop a job mid-run if needed.

Stop-DbaAgentJob -SqlInstance SQL-DB1 -Job Job1

Linked Servers:

Copy-DbaLinkedServer

Linked servers can be tricky to recreate. This handles the whole config in one shot.

Copy-DbaLinkedServer -Source SQL-DB1 -Destination SQL-DB2 -LinkedServer LS_SQL6 -Force

Some Advanced Commands:

Trace Flags and Startup Parameters

Apply trace flags across a group of servers in one go—super useful in cluster or AG setups.

Enable-DbaTraceFlag -SqlInstance SQL-DB1, SQL-DB2, SQL-DB3, SQL-DB4 -TraceFlag 3226
Set-DbaStartupParameter -SqlInstance SQL-DB1, SQL-DB2, SQL-DB3, SQL-DB4 -TraceFlag 3226

Clone a Database Schema

Need to replicate just the schema of a DB? This builds the full structure without any data.

$options = New-DbaScriptingOption
$options.driAllConstraints = $true

$transfer = New-DbaDbTransfer -SqlInstance SQL-DB1 -DestinationSqlInstance SQL-DB6 `
-Database DB_Test -DestinationDatabase DB_Test ` 
-SchemaOnly -CopyAll Tables, Schemas, Views, StoredProcedures, UserDefinedFunctions, UserDefinedDataTypes, UserDefinedTableTypes, PartitionSchemes, PartitionFunctions, Synonyms ` -ScriptingOption $options 

$transfer.DropDestinationObjectsFirst = $true
$transfer | Invoke-DbaDbTransfer -Verbose

Clone Logins with Password and SID

Clone a login to the same server with a new name and secure password handling.

Copy-DbaLogin -LoginRenameHashtable @{ user_test1 = "user_test2" } -Source SQL-DB1 -Destination SQL-DB1 -Login user_test1 -ObjectLevel -NewSid -ExcludeSystemLogins -Force

$SecurePassword = (Get-Credential NoUsernameNeeded).Password
$cred = New-Object System.Management.Automation.PSCredential ("user_test2", $SecurePassword)

Set-DbaLogin -SqlInstance SQL-DB1 -Login user_test2-SecurePassword $cred -Force

Copy-DbaLogin -Source 'SQL-DB1' -Destination 'SQL-DB2' -Login user_test2 -ExcludeSystemLogins -Force

Patching and Build Awareness

Staying current with SQL Server cumulative updates (CUs) can be tedious, especially across multiple nodes. dbatools gives you a way to automate both the planning and execution of patching operations.

# Update the Build Reference so dbatools is aware of the latest CU versions
Get-DbaBuildReference -MajorVersion 2022 -Update

# Create a list of servers that you want to patch, assuming all are secondary nodes
$ServerList = "SQL-DB2","SQL-DB4","SQL-DB6","SQL-DB8","SQL-DB10"

# Create a credential to pass in to the Update-DbaInstance command; this will prompt for your password
$cred = Get-Credential

# Set the version that you want to update to (SQL Server 2022 CU19)
# Note: You can verify the correct string with Get-DbaBuild -Version 2022
$version = '2022CU19'

# Start patching! The -Restart option will allow it to restart the SQL Server as needed

Update-DbaInstance -ComputerName $ServerList `
                   -Path '\\network\share\path\SQLSERVER\2022\CU19\' `
                   -Credential $cred `
                   -Version $version `
                   -Restart

Other Handy Admin Tools

Test-DbaDiskSpeed

Obtains I/O statistics based on the DMV sys.dm_io_virtual_file_stats

Test-DbaDiskSpeed -SqlInstance localhost -AggregateBy "Disk"

sp_WhoIsActive

Installs sp_WhoIsActive by Adam Machanic which is a priceless tool in terms of evaluating what is running on your instance

Install-DbaWhoIsActive -SqlInstance localhost -Database dba

First Responer Kit

Install the First responder kit from Brent Ozar Unlimited

Install-DbaFirstResponderKit -SqlInstance SQL123 -Database dba

SqlWatch

Adds lightweight, customizable performance monitoring.

Install-DbaSqlWatch -SqlInstance localhost -Database SqlWatch

Wrapping Up

Everything shown here scratches the surface. These are straightforward, reliable commands that help manage the daily grind of SQL Server administration. And one of the best things about them is this: you’re not limited to one server at a time. Most dbatools commands let you apply changes to multiple instances at once, which is a huge time-saver if you’re working across environments.

Of course, the commands here can be tweaked further, filtered, combined, parameterized, or built into larger workflows. That’s the beauty of working in PowerShell: nothing’s fixed and everything’s scriptable.