dbatools is a free PowerShell module with over 400 SQL Server best practice, administration, development and migration commands included. Currently, other components of SQL Server such as SSIS, SSRS and SSAS are not supported, but they are part of the overall goal.

The high number of commands may seem overwhelming, but think of it like learning SQL Server. Start with the basics like Logins, Jobs, or Backup/Restore and later on, you can move on to Extended Events. To make it easier, we’ve included simplified usage examples that can help you get started.

Want to help build dbatools? We invite you to join the team.

 

Categories

 

Migration

Copy-DbaAgentAlert
Copy-DbaAgentCategory
Copy-DbaAgentJob
Copy-DbaAgentOperator
Copy-DbaAgentProxyAccount
Copy-DbaAgentServer
Copy-DbaAgentSharedSchedule
Copy-DbaBackupDevice
Copy-DbaCentralManagementServer
Copy-DbaCredential
Copy-DbaCustomError
Copy-DbaDatabase
Copy-DbaDataCollector
Copy-DbaDbAssembly
Copy-DbaDbMail
Copy-DbaEndpoint
Copy-DbaExtendedEvent
Copy-DbaLinkedServer
Copy-DbaLogin
Copy-DbaPolicyManagement
Copy-DbaQueryStoreConfig
Copy-DbaResourceGovernor
Copy-DbaServerAudit
Copy-DbaServerAuditSpecification
Copy-DbaServerTrigger
Copy-DbaSpConfigure
Copy-DbaSsisCatalog
Copy-DbaSysDbUserObject
Start-DbaMigration
Test-DbaMigrationConstraint

Backup and Restore – Database

Backup-DbaDatabase
Format-DbaBackupInformation
Get-DbaBackupHistory
Get-DbaBackupInformation
Get-DbaDbExtentDiff
Get-DbaRestoreHistory
Get-DbaLastBackup
Invoke-DbaAdvancedRestore
Measure-DbaBackupThroughput
Read-DbaBackupHeader
Remove-DbaBackup
Restore-DbaBackupFromDirectory
Restore-DbaDatabase
Select-DbaBackupInformation
Test-DbaBackupInformation
Test-DbaRecoveryModel
Test-DbaLastBackup

Backup and Restore – Certificates

Backup-DbaDbCertificate
Backup-DbaDbMasterKey
Restore-DbaDbCertificate

Diagnostics and Performance

Clear-DbaConnectionPool
Clear-DbaPlanCache
Clear-DbaWaitStatistics
Disable-DbaTraceFlag
Enable-DbaTraceFlag
Export-DbaDiagnosticQuery
Export-DbaExecutionPlan
Get-DbaCpuUsage
Get-DbaDbVirtualLogFile
Get-DbaDump
Get-DbaEstimatedCompletionTime
Get-DbaExecutionPlan
Get-DbaOpenTransaction
Get-DbaPlanCache
Get-DbaProcess
Get-DbaQueryExecutionTime
Get-DbaTopResourceUsage
Get-DbaTraceFlag
Get-DbaWaitingTask
Get-DbaWaitResource
Get-DbaWaitStatistic
Invoke-DbaDiagnosticQuery
Measure-DbaDiskSpaceRequirement
Save-DbaDiagnosticQueryScript
Stop-DbaProcess
Test-DbaDbCompression
Test-DbaDbVirtualLogFile
Test-DbaDiskSpeed

Traces and Profiler

Get-DbaTrace
Read-DbaTraceFile
Remove-DbaTrace
Start-DbaTrace
Stop-DbaTrace

Extended Events

ConvertTo-DbaXESession
Copy-DbaXESessionTemplate
Export-DbaXECsv
Export-DbaXESessionTemplate
Get-DbaXEObject
Get-DbaXESession
Get-DbaXESessionTarget
Get-DbaXESessionTemplate
Get-DbaXESmartTarget
Get-DbaXEStore
Import-DbaXESessionTemplate
Invoke-DbaXeReplay
New-DbaXESession
New-DbaXESmartCsvWriter
New-DbaXESmartEmail
New-DbaXESmartQueryExec
New-DbaXESmartReplay
New-DbaXESmartTableWriter
Read-DbaXEFile
Remove-DbaXESession
Remove-DbaXESmartTarget
Start-DbaXESession
Start-DbaXESmartTarget
Stop-DbaXESession
Stop-DbaXESmartTarget
Watch-DbaXESession

Resource Governor

Get-DbaResourceGovernor
Get-DbaRgClassifierFunction
Get-DbaRgResourcePool
Get-DbaRgWorkloadGroup

PerfMon

Add-DbaPfDataCollectorCounter
Export-DbaPfDataCollectorSetTemplate
Get-DbaPfAvailableCounter
Get-DbaPfDataCollector
Get-DbaPfDataCollectorCounter
Get-DbaPfDataCollectorCounterSample
Get-DbaPfDataCollectorSet
Get-DbaPfDataCollectorSetTemplate
Import-DbaPfDataCollectorSetTemplate
Invoke-DbaPfRelog
Remove-DbaPfDataCollectorSet
Remove-DbaPfDataCollectorCounter
Start-DbaPfDataCollectorSet
Stop-DbaPfDataCollectorSet

Security and Encryption

Add-DbaComputerCertificate
Enable-DbaForceNetworkEncryption
Disable-DbaForceNetworkEncryption
Get-DbaComputerCertificate
Get-DbaCredential
Get-DbaForceNetworkEncryption
Get-DbaNetworkCertificate
Get-DbaPermission
Get-DbaPrivilege
Get-DbaServerAudit
Get-DbaServerAuditSpecification
Get-DbaUserPermission
New-DbaComputerCertificate
New-DbaCredential
New-DbaServiceMasterKey
Read-DbaAuditFile
Remove-DbaComputerCertificate
Remove-DbaNetworkCertificate
Set-DbaPrivilege
Set-DbaNetworkCertificate

High Availability

Disable-DbaAgHadr
Enable-DbaAgHadr
Get-DbaAgDatabase
Get-DbaAgHadr
Get-DbaAgListener
Get-DbaAgReplica
Get-DbaAvailabilityGroup
Get-DbaClusterNode

Log Shipping

Get-DbaLogShippingError
Invoke-DbaLogShipping
Invoke-DbaLogShippingRecovery
Test-DbaLogShippingStatus

Databases

Expand-DbaTLogResponsibly
Find-DbaDatabase
Find-DbaDbGrowthEvent
Find-DbaDisabledIndex
Find-DbaDuplicateIndex
Get-DbaAvailableCollation
Get-DbaDatabase
Get-DbaDbAssembly
Get-DbaDbCertificate
Get-DbaDbCheckConstraint
Get-DbaDbCompression
Get-DbaDbEncryption
Get-DbaDbFile
Get-DbaDbForeignKey
Get-DbaDbMasterKey
Get-DbaDbPageInfo
Get-DbaDbPartitionFunction
Get-DbaDbPartitionScheme
Get-DbaDbQueryStoreOption
Get-DbaDbRecoveryModel
Get-DbaDbRole
Get-DbaDbSnapshot
Get-DbaDbSpace
Get-DbaDbState
Get-DbaDbStoredProcedure
Get-DbaDbTrigger
Get-DbaDbUdf
Get-DbaDbUser
Get-DbaDbView
Get-DbaHelpIndex
Get-DbaSuspectPage
Invoke-DbaDbClone
Invoke-DbaDbDecryptObject
Invoke-DbaDbShrink
Invoke-DbaDbUpgrade
New-DbaDbCertificate
New-DbaDbMasterKey
Remove-DbaDatabase
Remove-DbaDatabaseSafely
Remove-DbaDbCertificate
Remove-DbaDbMasterKey
Rename-DbaDatabase
Set-DbaDbOwner
Set-DbaDbQueryStoreOption
Set-DbaDbState
Set-DbaDbCompression
Set-DbaDbRecoveryModel
Show-DbaDbList
Test-DbaConnectionAuthScheme
Test-DbaDbCollation
Test-DbaDbCompatibility
Test-DbaDbOwner
Test-DbaDbCompression
Test-DbaIdentityUsage
Test-DbaOptimizeForAdHoc

tempdb

Get-DbaTempdbUsage
Set-DbaTempdbConfig
Test-DbaTempDbConfiguration

Snapshots

Get-DbaDbSnapshot
New-DbaDbSnapshot
Restore-DbaDbSnapshot
Remove-DbaDbSnapshot

Login and User Management

Find-DbaLoginInGroup
Get-DbaDbRole
Get-DbaServerRole
Get-DbaLogin
Get-DbaOrphanUser
New-DbaDbUser
New-DbaLogin
Remove-DbaDbUser
Remove-DbaOrphanUser
Remove-DbaLogin
Rename-DbaLogin
Repair-DbaOrphanUser
Set-DbaLogin
Sync-DbaLoginPermission
Test-DbaLoginPassword
Test-DbaWindowsLogin
Watch-DbaDbLogin

Export

Export-DbaAvailabilityGroup
Export-DbaCredential
Export-DbaDacPackage
Export-DbaInstance
Export-DbaLinkedServer
Export-DbaLogin
Export-DbaRegisteredServer
Export-DbaScript
Export-DbaSpConfigure
Export-DbaUser
New-DbaScriptingOption

Detach and Attach

Get-DbaDetachedDatabaseInfo
Mount-DbaDatabase
Dismount-DbaDatabase

SQL Agent

Get-DbaAgentAlert
Get-DbaAgentJob
Get-DbaAgentJobCategory
Get-DbaAgentJobHistory
Get-DbaAgentJobOutputFile
Get-DbaAgentJobStep
Get-DbaAgentLog
Get-DbaAgentOperator
Get-DbaAgentProxy
Get-DbaAgentSchedule
Get-DbaRunningJob
New-DbaAgentJob
New-DbaAgentJobCategory
New-DbaAgentJobStep
New-DbaAgentProxy
New-DbaAgentSchedule
Set-DbaAgentAlert
Set-DbaAgentJob
Set-DbaAgentJobCategory
Set-DbaAgentJobOutputFile
Set-DbaAgentJobStep
Set-DbaAgentSchedule
Remove-DbaAgentJob
Remove-DbaAgentJobCategory
Remove-DbaAgentJobStep
Remove-DbaAgentSchedule
Set-DbaJobOwner
Start-DbaAgentJob
Stop-DbaAgentJob
Test-DbaJobOwner

Mail and logging

Get-DbaDbMail
Get-DbaDbMailAccount
Get-DbaDbMailConfig
Get-DbaDbMailHistory
Get-DbaDbMailLog
Get-DbaDbMailProfile
Get-DbaDbMailServer
Get-DbaErrorLog
Get-DbaErrorLogConfig
Get-DbaMaintenanceSolutionLog
Get-DbaWindowsLog
Invoke-DbaCycleErrorLog
Set-DbaErrorLogConfig

Writing to SQL Tables

ConvertTo-DbaDataTable
Copy-DbaTableData
Write-DbaDataTable

Services

Get-DbaService
Restart-DbaService
Start-DbaService
Stop-DbaService
Update-DbaServiceAccount

Server Management

Get-DbaClientProtocol
Get-DbaComputerSystem
Get-DbaDiskSpace
Get-DbaLocaleSetting
Get-DbaMemoryUsage
Get-DbaNetworkActivity
Get-DbaOperatingSystem
Get-DbaPageFileSetting
Get-DbaRegistryRoot
Get-DbaServerProtocol

File System and Storage

Get-DbaDefaultPath
Get-DbaFile
New-DbaDirectory
Remove-DbaBackup
Show-DbaServerFileSystem
Test-DbaDiskAlignment
Test-DbaDiskAllocation
Test-DbaPath

Deployment

Export-DbaDacPackage
New-DbaDacProfile
Publish-DbaDacPackage

Utilities

Connect-DbaInstance
ConvertTo-DbaTimeline
Test-DbaBuild
Get-DbaBuildReference
Get-DbaDependency
Get-DbaLastGoodCheckDb
Get-DbaProductKey
Get-DbaSchemaChangeHistory
Get-DbaServerInstallDate
Get-DbaUptime
Import-DbaCsvToSql
Invoke-DbaQuery
Invoke-DbaBalanceDataFiles
Invoke-DbaDbDecryptObject
Invoke-Sqlcmd2
Read-DbaTransactionLog
Repair-DbaServerName
Reset-DbaAdmin
Set-DbaMaxDop
Set-DbaPowerPlan
Test-DbaPowerPlan
Test-DbaMaxDop
Test-DbaServerName

Connection Strings

New-DbaConnectionString
New-DbaConnectionStringBuilder

Network and connectivity

Get-DbaConnection
Get-DbaTcpPort
Resolve-DbaNetworkName
Set-DbaTcpPort
Test-DbaConnection
Test-DbaLinkedServerConnection
Test-DbaNetworkLatency

Service Principal Names (SPNs)

Get-DbaSpn
Remove-DbaSpn
Set-DbaSpn
Test-DbaSpn

SQL Client Configuration

Get-DbaClientAlias
New-DbaClientAlias
Remove-DbaClientAlias

SSIS

Get-DbaSsisEnvironmentVariable
Get-DbaSsisExecutionHistory
New-DbaSsisCatalog

Policy-Based Management

Get-DbaPbmCategory
Get-DbaPbmCategorySubscription
Get-DbaPbmCondition
Get-DbaPbmObjectSet
Get-DbaPbmPolicy
Get-DbaPbmStore

General

Get-DbaBackupDevice
Get-DbaCustomError
Get-DbaEndpoint
Get-DbaFeature
Get-DbaInstanceProperty
Get-DbaInstanceUserOption
Get-DbaLinkedServer
Get-DbaModule
Get-DbaMsdtc
Get-DbaRoleMember
Get-DbaTable
Get-DbaServerTrigger

Finders

Find-DbaAgentJob
Find-DbaBackup
Find-DbaDatabase
Find-DbaDbGrowthEvent
Find-DbaDuplicateIndex
Find-DbaInstance
Find-DbaOrphanedFile
Find-DbaSimilarTable
Find-DbaStoredProcedure
Find-DbaTrigger
Find-DbaUnusedIndex
Find-DbaUserObject
Find-DbaView

SQL Management Objects

Get-DbaManagementObject
Test-DbaManagementObject

sp_configure

Get-DbaSpConfigure
Export-DbaSpConfigure
Import-DbaSpConfigure
Set-DbaSpConfigure

System startup

Set-DbaStartupParameter
Get-DbaStartupParameter

Max Memory

Get-DbaMaxMemory
Test-DbaMaxMemory
Set-DbaMaxMemory

Replication

Get-DbaDistributor
Get-DbaRepPublication
Test-DbaRepLatency

Registered Servers

Add-DbaRegisteredServer
Add-DbaRegisteredServerGroup
Get-DbaRegisteredServer
Get-DbaRegisteredServerGroup
Get-DbaRegisteredServerStore
Import-DbaRegisteredServer
Move-DbaRegisteredServer
Move-DbaRegisteredServerGroup
Remove-DbaRegisteredServer
Remove-DbaRegisteredServerGroup

Community Tools

Export-DbaDiagnosticQuery
Install-DbaFirstResponderKit
Install-DbaMaintenanceSolution
Install-DbaWhoIsActive
Invoke-DbaDiagnosticQuery
Invoke-DbaWhoisActive
Save-DbaDiagnosticQueryScript

dbatools Computer Management

Get-DbaCmConnection
Get-DbaCmObject
New-DbaCmConnection
Set-DbaCmConnection
Remove-DbaCmConnection
Test-DbaCmConnection

dbatools Support tools

Find-DbaCommand
Get-DbatoolsConfig
Get-DbatoolsConfigValue
Get-DbatoolsLog
Invoke-DbatoolsRenameHelper
Register-DbatoolsConfig
Set-DbatoolsConfig
New-DbatoolsSupportPackage
Update-Dbatools

dbatools update watcher

Install-DbaWatchUpdate
Uninstall-DbaWatchUpdate
Watch-DbaUpdate