dbatools is a free PowerShell module with over 500 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-DbaAgentJobCategory
Copy-DbaAgentJob
Copy-DbaAgentOperator
Copy-DbaAgentProxy
Copy-DbaAgentServer
Copy-DbaAgentSchedule
Copy-DbaBackupDevice
Copy-DbaCredential
Copy-DbaCustomError
Copy-DbaDatabase
Copy-DbaDataCollector
Copy-DbaDbAssembly
Copy-DbaDbMail
Copy-DbaDbQueryStoreOption
Copy-DbaEndpoint
Copy-DbaInstanceAudit
Copy-DbaInstanceAuditSpecification
Copy-DbaInstanceTrigger
Copy-DbaLinkedServer
Copy-DbaLogin
Copy-DbaPolicyManagement
Copy-DbaRegServer
Copy-DbaResourceGovernor
Copy-DbaSpConfigure
Copy-DbaStartupProcedure
Copy-DbaSysDbUserObject
Copy-DbaXESession
Start-DbaMigration
Test-DbaMigrationConstraint

Backup and Restore – Database

Backup-DbaDatabase
Get-DbaDbRecoveryModel
Format-DbaBackupInformation
Get-DbaDbBackupHistory
Get-DbaBackupInformation
Get-DbaDbExtentDiff
Get-DbaDbRestoreHistory
Get-DbaLastBackup
Invoke-DbaAdvancedRestore
Measure-DbaBackupThroughput
Read-DbaBackupHeader
Remove-DbaBackup
Remove-DbaDbBackupRestoreHistory
Restore-DbaDatabase
Select-DbaBackupInformation
Test-DbaBackupInformation
Test-DbaDbRecoveryModel
Test-DbaLastBackup

Backup and Restore – Certificates

Backup-DbaComputerCertificate
Backup-DbaDbCertificate
Backup-DbaDbMasterKey
Backup-DbaServiceMasterKey
Restore-DbaDbCertificate

Diagnostics and Performance

Clear-DbaConnectionPool
Clear-DbaLatchStatistics
Clear-DbaPlanCache
Clear-DbaWaitStatistics
Disable-DbaTraceFlag
Enable-DbaTraceFlag
Export-DbaExecutionPlan
Get-DbaCpuRingBuffer
Get-DbaCpuUsage
Get-DbaDbVirtualLogFile
Get-DbaDump
Get-DbaEstimatedCompletionTime
Get-DbaExecutionPlan
Get-DbaExternalProcess
Get-DbaIoLatency
Get-DbaLatchStatistic
Get-DbaOpenTransaction
Get-DbaPlanCache
Get-DbaProcess
Get-DbaQueryExecutionTime
Get-DbaSpinLockStatistic
Get-DbaTopResourceUsage
Get-DbaTraceFlag
Get-DbaWaitingTask
Get-DbaWaitResource
Get-DbaWaitStatistic
Measure-DbaDbVirtualLogFile
Measure-DbaDiskSpaceRequirement
Stop-DbaExternalProcess
Stop-DbaProcess
Test-DbaDiskSpeed

DBCC

Get-DbaDbccHelp
Get-DbaDbccMemoryStatus
Get-DbaDbccProcCache
Get-DbaDbccSessionBuffer
Get-DbaDbccStatistic
Get-DbaDbccUserOption
Get-DbaDbDbccOpenTran
Invoke-DbaDbccDropCleanBuffer
Invoke-DbaDbccFreeCache
Invoke-DbaDbDbccCheckConstraint
Invoke-DbaDbDbccCleanTable
Invoke-DbaDbDbccUpdateUsage

Traces and Profiler

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

Extended Events

ConvertTo-DbaXESession
Copy-DbaXESessionTemplate
Export-DbaXECsv
Export-DbaXESession
Export-DbaXESessionTemplate
Get-DbaXEObject
Get-DbaXESession
Get-DbaXESessionTarget
Get-DbaXESessionTargetFile
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
New-DbaRgResourcePool
New-DbaRgWorkloadGroup
Remove-DbaRgResourcePool
Remove-DbaRgWorkloadGroup
Set-DbaResourceGovernor
Set-DbaRgResourcePool
Set-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

Availability Groups

Add-DbaAgDatabase
Add-DbaAgListener
Add-DbaAgReplica
Disable-DbaAgHadr
Enable-DbaAgHadr
Get-DbaAgBackupHistory
Get-DbaAgDatabase
Get-DbaAgHadr
Get-DbaAgListener
Get-DbaAgReplica
Get-DbaAvailabilityGroup
Grant-DbaAgPermission
Invoke-DbaAgFailover
Join-DbaAvailabilityGroup
New-DbaAvailabilityGroup
Remove-DbaAgDatabase
Remove-DbaAgListener
Remove-DbaAgReplica
Remove-DbaAvailabilityGroup
Resume-DbaAgDbDataMovement
Revoke-DbaAgPermission
Set-DbaAgListener
Set-DbaAgReplica
Set-DbaAvailabilityGroup
Suspend-DbaAgDbDataMovement
Sync-DbaAvailabilityGroup
Test-DbaAvailabilityGroup

Windows Server Failover Cluster

Get-DbaWsfcAvailableDisk
Get-DbaWsfcCluster
Get-DbaWsfcDisk
Get-DbaWsfcNetwork
Get-DbaWsfcNetworkInterface
Get-DbaWsfcNode
Get-DbaWsfcResource
Get-DbaWsfcResourceType
Get-DbaWsfcRole
Get-DbaWsfcSharedVolume

Mirroring

Add-DbaDbMirrorMonitor
Get-DbaDbMirror
Get-DbaDbMirrorMonitor
Invoke-DbaDbMirrorFailover
Invoke-DbaDbMirroring
Remove-DbaDbMirror
Remove-DbaDbMirror
Remove-DbaDbMirrorMonitor
Repair-DbaDbMirror
Set-DbaDbMirror

tempdb

Get-DbaTempdbUsage
Set-DbaTempdbConfig
Test-DbaTempdbConfig

Data Masking

Invoke-DbaDbDataMasking
Invoke-DbaDbPiiScan
New-DbaDbMaskingConfig
Test-DbaDbDataMaskingConfig

Certificates and Encryption

Copy-DbaDbCertificate
Disable-DbaDbEncryption
Enable-DbaDbEncryption
Get-DbaDbEncryptionKey
New-DbaDbEncryptionKey
Remove-DbaDbEncryptionKey
Start-DbaDbEncryption
Stop-DbaDbEncryption
Test-DbaBackupEncrypted

Log Shipping

Get-DbaDbLogShipError
Invoke-DbaDbLogShipping
Invoke-DbaDbLogShipRecovery
Remove-DbaDbLogShipping
Test-DbaDbLogShipStatus

Databases

Get-DbaDatabase
Get-DbaDbAssembly
Get-DbaDbCertificate
Get-DbaDbCheckConstraint
Get-DbaDbCompatibility
Get-DbaDbFeatureUsage
Get-DbaDbIdentity
Get-DbaDbMemoryUsage
Get-DbaDbMirrorMonitor
Get-DbaDbObjectTrigger
Get-DbaDbPageInfo
Get-DbaDbPartitionFunction
Get-DbaDbPartitionScheme
Get-DbaDbQueryStoreOption
Get-DbaDbSchema
Get-DbaDbSharePoint
Get-DbaDbSpace
Get-DbaDbState
Get-DbaDbStoredProcedure
Get-DbaDbSynonym
Get-DbaDbTable
Get-DbaDbTrigger
Get-DbaDbUdf
Get-DbaDbUserDefinedTableType
Get-DbaDbView
Get-DbaHelpIndex
Get-DbaSuspectPage
Invoke-DbaDbClone
Invoke-DbaDbShrink
Invoke-DbaDbUpgrade
New-DbaDatabase
New-DbaDbSchema
New-DbaDbSynonym
Remove-DbaDatabase
Remove-DbaDatabaseSafely
Remove-DbaDbSchema
Remove-DbaDbSynonym
Remove-DbaDbTable
Remove-DbaDbUdf
Remove-DbaDbView
Rename-DbaDatabase
Set-DbaDbCompatibility
Set-DbaDbIdentity
Set-DbaDbOwner
Set-DbaDbQueryStoreOption
Set-DbaDbRecoveryModel
Set-DbaDbSchema
Set-DbaDbState
Show-DbaDbList
Test-DbaConnectionAuthScheme
Test-DbaDbCollation
Test-DbaDbCompatibility
Test-DbaDbOwner
Test-DbaDbQueryStore
Test-DbaOptimizeForAdHoc

Login and User Management

Add-DbaDbRoleMember
Add-DbaServerRoleMember
Get-DbaDbOrphanUser
Get-DbaDbRole
Get-DbaDbRoleMember
Get-DbaDbUser
Get-DbaLogin
Get-DbaServerRole
Get-DbaServerRoleMember
New-DbaDbRole
New-DbaDbUser
New-DbaLogin
New-DbaServerRole
Remove-DbaDbOrphanUser
Remove-DbaDbRole
Remove-DbaDbRoleMember
Remove-DbaDbUser
Remove-DbaLogin
Remove-DbaServerRole
Remove-DbaServerRoleMember
Rename-DbaLogin
Repair-DbaDbOrphanUser
Set-DbaLogin
Sync-DbaLoginPermission
Test-DbaLoginPassword
Test-DbaWindowsLogin
Watch-DbaDbLogin

Security and Encryption

Add-DbaComputerCertificate
Disable-DbaForceNetworkEncryption
Disable-DbaHideInstance
Enable-DbaForceNetworkEncryption
Enable-DbaHideInstance
Get-DbaComputerCertificate
Get-DbaCredential
Get-DbaDbAsymmetricKey
Get-DbaDbEncryption
Get-DbaDbMasterKey
Get-DbaExtendedProtection
Get-DbaForceNetworkEncryption
Get-DbaHideInstance
Get-DbaInstanceAudit
Get-DbaInstanceAuditSpecification
Get-DbaNetworkCertificate
Get-DbaPermission
Get-DbaPrivilege
Get-DbaUserPermission
New-DbaComputerCertificate
New-DbaComputerCertificateSigningRequest
New-DbaCredential
New-DbaDbAsymmetricKey
New-DbaDbCertificate
New-DbaDbMasterKey
New-DbaServiceMasterKey
Read-DbaAuditFile
Remove-DbaComputerCertificate
Remove-DbaCredential
Remove-DbaDbAsymmetricKey
Remove-DbaDbCertificate
Remove-DbaDbMasterKey
Remove-DbaNetworkCertificate
Set-DbaExtendedProtection
Set-DbaNetworkCertificate
Set-DbaPrivilege
Test-DbaComputerCertificateExpiration

Export

Export-DbaCredential
Export-DbaDacPackage
Export-DbaDbRole
Export-DbaDbTableData
Export-DbaInstance
Export-DbaLinkedServer
Export-DbaLogin
Export-DbaRegServer
Export-DbaRepServerSetting
Export-DbaScript
Export-DbaServerRole
Export-DbaSpConfigure
Export-DbaSysDbUserObject
Export-DbaUser

Detach and Attach

Dismount-DbaDatabase
Get-DbaDbDetachedFileInfo
Mount-DbaDatabase

SQL Agent

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

Mail and Logging

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

Table Data

ConvertTo-DbaDataTable
Copy-DbaDbTableData
Copy-DbaDbViewData
Get-DbaDbCompression
Get-DbaDbForeignKey
Get-DbaDbSequence
New-DbaDbSequence
New-DbaDbTable
Remove-DbaDbData
Remove-DbaDbSequence
Remove-DbaDbTableData
Select-DbaDbSequenceNextValue
Set-DbaDbCompression
Set-DbaDbSequence
Test-DbaDbCompression
Test-DbaIdentityUsage
Write-DbaDbTableData

Server Management

Get-DbaAvailableCollation
Get-DbaClientProtocol
Get-DbaComputerSystem
Get-DbaInstanceProtocol
Get-DbaLocaleSetting
Get-DbaMemoryUsage
Get-DbaOperatingSystem
Get-DbaPageFileSetting
Get-DbaRegistryRoot

Endpoints

Get-DbaEndpoint
New-DbaEndpoint
Remove-DbaEndpoint
Set-DbaEndpoint
Start-DbaEndpoint
Stop-DbaEndpoint
Test-DbaEndpoint

Snapshots

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

File System and Storage

Expand-DbaDbLogFile
Get-DbaDbFile
Get-DbaDbFileGroup
Get-DbaDbFileGrowth
Get-DbaDbFileMapping
Get-DbaDbLogSpace
Get-DbaDefaultPath
Get-DbaDiskSpace
Get-DbaFile
Move-DbaDbFile
New-DbaDbFileGroup
New-DbaDirectory
Remove-DbaDbFileGroup
Set-DbaDbFileGroup
Set-DbaDbFileGrowth
Set-DbaDefaultPath
Show-DbaInstanceFileSystem
Test-DbaDiskAlignment
Test-DbaDiskAllocation
Test-DbaPath

Services

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

Deployment

Get-DbaInstalledPatch
Get-DbaKbUpdate
Install-DbaInstance
Invoke-DbaAdvancedInstall
Invoke-DbaAdvancedUpdate
New-DbaDacOption
New-DbaDacProfile
Publish-DbaDacPackage
Save-DbaKbUpdate
Update-DbaInstance

Data Generation

Get-DbaRandomizedDataset
Get-DbaRandomizedDatasetTemplate
Get-DbaRandomizedType
Get-DbaRandomizedValue
Invoke-DbaDbDataGenerator
New-DbaDbDataGeneratorConfig
Test-DbaDbDataGeneratorConfig

Utilities

ConvertTo-DbaTimeline
Get-DbaBuild
Get-DbaDependency
Get-DbaInstanceInstallDate
Get-DbaLastGoodCheckDb
Get-DbaPowerPlan
Get-DbaProductKey
Get-DbaSchemaChangeHistory
Get-DbaUptime
Import-DbaCsv
Invoke-DbaBalanceDataFiles
Invoke-DbaDbDecryptObject
Invoke-DbaQuery
New-DbaSqlParameter
Join-DbaPath
Read-DbaTransactionLog
Repair-DbaInstanceName
Reset-DbaAdmin
Resolve-DbaPath
Select-DbaObject
Set-DbaMaxDop
Set-DbaPowerPlan
Test-DbaBuild
Test-DbaInstanceName
Test-DbaMaxDop
Test-DbaPowerPlan
Update-DbaBuildReference

Connections

Connect-DbaInstance
Disconnect-DbaInstance
Get-DbaConnectedInstance
Get-DbaConnection
Test-DbaConnection

Connection Strings

New-DbaAzAccessToken
New-DbaConnectionString
New-DbaConnectionStringBuilder

Network and Firewall

Get-DbaFirewallRule
Get-DbaNetworkActivity
Get-DbaNetworkConfiguration
Get-DbaTcpPort
New-DbaFirewallRule
Remove-DbaFirewallRule
Resolve-DbaNetworkName
Set-DbaNetworkConfiguration
Set-DbaTcpPort
Test-DbaNetworkLatency

Service Principal Names (SPNs)

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

SQL Client Configuration

Get-DbaClientAlias
New-DbaClientAlias
Remove-DbaClientAlias

Policy-Based Management

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

General

Add-DbaExtendedProperty
Get-DbaBackupDevice
Get-DbaCustomError
Get-DbaDeprecatedFeature
Get-DbaExtendedProperty
Get-DbaFeature
Get-DbaInstanceProperty
Get-DbaInstanceTrigger
Get-DbaInstanceUserOption
Get-DbaMemoryCondition
Get-DbaModule
Get-DbaMsdtc
Get-DbaOleDbProvider
Get-DbaSsisExecutionHistory
Invoke-DbaDbTransfer
New-DbaCustomError
New-DbaDbTransfer
New-DbaScriptingOption
Remove-DbaCustomError
Remove-DbaExtendedProperty
Set-DbaExtendedProperty
Test-DbaDeprecatedFeature

FileStream

Disable-DbaFilestream
Enable-DbaFilestream
Get-DbaFileStream

Linked Servers

Get-DbaLinkedServer
Get-DbaLinkedServerLogin
New-DbaLinkedServerLogin
Remove-DbaLinkedServer
Remove-DbaLinkedServerLogin
Test-DbaLinkedServerConnection

Lookup (Find)

Find-DbaAgentJob
Find-DbaBackup
Find-DbaDatabase
Find-DbaDbDisabledIndex
Find-DbaDbDuplicateIndex
Find-DbaDbGrowthEvent
Find-DbaDbUnusedIndex
Find-DbaInstance
Find-DbaLoginInGroup
Find-DbaOrphanedFile
Find-DbaSimilarTable
Find-DbaStoredProcedure
Find-DbaTrigger
Find-DbaUserObject
Find-DbaView

SQL Management Objects

Get-DbaManagementObject
Test-DbaManagementObject

sp_configure

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

System startup

Disable-DbaStartupProcedure
Enable-DbaStartupProcedure
Get-DbaStartupParameter
Get-DbaStartupProcedure
Set-DbaStartupParameter

Max Memory

Get-DbaMaxMemory
Set-DbaMaxMemory
Test-DbaMaxMemory

Replication

Export-DbaRepServerSetting
Get-DbaRepDistributor
Get-DbaRepPublication
Get-DbaRepServer
Test-DbaRepLatency

Registered Servers

Add-DbaRegServer
Add-DbaRegServerGroup
Export-DbaRegServer
Get-DbaRegServer
Get-DbaRegServerGroup
Get-DbaRegServerStore
Import-DbaRegServer
Move-DbaRegServer
Move-DbaRegServerGroup
Remove-DbaRegServer
Remove-DbaRegServerGroup

Community Tools

Export-DbaDiagnosticQuery
Get-DbaMaintenanceSolutionLog
Install-DbaDarlingData
Install-DbaFirstResponderKit
Install-DbaMaintenanceSolution
Install-DbaMultiTool
Install-DbaSqlWatch
Install-DbaWhoIsActive
Invoke-DbaDiagnosticQuery
Invoke-DbaWhoisActive
New-DbaDiagnosticAdsNotebook
Save-DbaCommunitySoftware
Save-DbaDiagnosticQueryScript
Uninstall-DbaSqlWatch
Update-DbaMaintenanceSolution

dbatools Computer Management

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

dbatools Support tools

Export-DbatoolsConfig
Find-DbaCommand
Get-DbatoolsChangeLog
Get-DbatoolsConfig
Get-DbatoolsConfigValue
Get-DbatoolsError
Get-DbatoolsLog
Get-DbatoolsPath
Import-DbatoolsConfig
Invoke-DbatoolsFormatter
Invoke-DbatoolsRenameHelper
Measure-DbatoolsImport
New-DbatoolsSupportPackage
Register-DbatoolsConfig
Reset-DbatoolsConfig
Set-DbatoolsConfig
Set-DbatoolsPath
Unregister-DbatoolsConfig
Update-Dbatools

dbatools update watcher

Install-DbatoolsWatchUpdate
Uninstall-DbatoolsWatchUpdate
Watch-DbatoolsUpdate