A personal reference of common dbaTools commands for SQL Server administration tasks. Covers installation, configuration, and day-to-day DBA operations.
Requirements: PowerShell 5.1+ or PowerShell 7+, and sufficient permissions on target SQL Server instances.
Install-Module dbatoolsdbatools releases frequently — older versions can have bugs or missing features. Run this periodically to stay current:
Update-Module dbatoolsThird-party module for formatting PowerShell objects as proper markdown tables. Required for the Database Inventory commands below:
Install-Module Format-MarkdownPowerShell blocks unsigned scripts by default. RemoteSigned allows local scripts while requiring remote scripts to be signed:
Set-ExecutionPolicy RemoteSigned -Scope CurrentUserSQL Server 2022+ (and some patched older versions) enforce encrypted connections by default. Dev and internal servers typically use self-signed certificates, which the client rejects unless you explicitly trust them. This setting persists across sessions:
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -PassThru | Register-DbatoolsConfigA set of independent checks for validating a server's configuration and performance baseline. These can be run individually or as part of a new server setup review.
Note: Some commands use -ComputerName (OS-level, no SQL required) vs. -SqlInstance (requires SQL connectivity).
Updates the local dbatools build reference cache from the internet, then returns the build information for an instance. Run this before Test-DbaBuild if the instance is showing as unknown:
Get-DbaBuildReference -SqlInstance <ServerName> -UpdateChecks whether a SQL Server instance is on the latest known build. Useful for patch compliance audits:
Test-DbaBuild -SqlInstance <ServerName> -Latest |
Select-Object SqlInstance, NameLevel, SPLevel, CULevel, KBLevel, Build, BuildTarget, Compliant |
Format-Table -AutoSizeChecks TempDB configuration against best practices, including file count, sizing, and autogrowth settings:
Test-DbaTempDbConfig -SqlInstance <ServerName> | Format-Table -AutoSizeVerifies partitions are aligned to 64K boundaries. Misalignment causes unnecessary I/O overhead:
Test-DbaDiskAlignment -ComputerName <ServerName> | Format-Table -AutoSizeChecks that NTFS allocation unit size is set to 64K on data volumes, per SQL Server best practice:
Test-DbaDiskAllocation -ComputerName <ServerName> | Format-Table -AutoSizeMeasures read/write throughput on volumes hosting SQL Server files:
Test-DbaDiskSpeed -SqlInstance <ServerName> | Format-Table -AutoSizeTests round-trip latency between the client and the SQL Server instance:
Test-DbaNetworkLatency -SqlInstance <ServerName> | Format-Table -AutoSizeTests whether MAXDOP is configured per best practice, and optionally applies the recommended value:
Test-DbaMaxDop -SqlInstance <ServerName> | Set-DbaMaxDop | Format-Table -AutoSizeVerifies the server is running the High Performance power plan — anything else throttles CPU:
Test-DbaPowerPlan -ComputerName <ServerName> | Format-Table -AutoSizeTests whether max server memory is configured appropriately for the host's physical RAM.
The filter limits Set-DbaMaxMemory to only instances where the configured max exceeds total RAM:
Test-DbaMaxMemory -SqlInstance <ServerName> |
Where-Object { $_.MaxValue -gt $_.Total } |
Set-DbaMaxMemoryValidates that SQL Server SPNs are correctly registered in Active Directory. Accepts a comma-separated list for checking multiple instances at once:
Test-DbaSpn -ComputerName <ServerName>| Format-Table -AutoSizeStandard third-party tools deployed to SQL Server instances for monitoring and maintenance.
Update the -Database target per your environment conventions.
Active session monitoring stored procedure by Adam Machanic:
Install-DbaWhoIsActive -SqlInstance <ServerName> -Database masterIndex maintenance, backups, and integrity checks. -CleanupTime sets job history retention in hours:
Install-DbaMaintenanceSolution -SqlInstance <ServerName> -Database master -CleanupTime 72 -InstallJobs -VerboseBrent Ozar's diagnostic toolkit (sp_Blitz, sp_BlitzFirst, sp_BlitzIndex, etc.):
Install-DbaFirstResponderKit -SqlInstance <ServerName> -Database DBACreates standard SQL Server Agent alerts for severity 17–25 errors and critical I/O errors (823, 824, 825):
Install-DbaAgentAdminAlert -SqlInstance <ServerName>Diagnostic stored procedures including sp_PressureDetector, sp_HumanEvents, and sp_QuickieStore:
Install-DbaDarlingData -SqlInstance <ServerName> -Database <DatabaseName>Installs Microsoft's SqlPackage CLI, used for DACPAC/BACPAC export and import operations:
Install-DbaSqlPackageRead-only commands for gathering state across one or more instances. Useful for audits, onboarding a new server, or spot-checking configuration.
Returns volume-level disk space for a server, sorted by computer and volume name:
Get-DbaDiskSpace -ComputerName <ServerName> |
Sort-Object ComputerName, Name |
Format-Table -AutoSizeLists all SQL Server features installed on the host:
Get-DbaFeature -ComputerName <ServerName> -Verbose | Format-Table -AutoSizeReturns the Database Mail configuration for one or more instances. Accepts a comma-separated instance list:
Get-DbaDbMail -SqlInstance <ServerName> | Format-Table -AutoSize
Get-DbaDbMailAccount -SqlInstance <ServerName> -Account 'Default SMTP Account' | Format-Table -AutoSize
Get-DbaDbMailProfile -SqlInstance <ServerName> | Format-Table -AutoSizeReturns current tempdb session usage. Out-GridView is useful here for sorting and filtering interactively:
Get-DbaTempdbUsage -SqlInstance <ServerName> | Out-GridViewReturns SQL Server services on a host with state and start mode:
Get-DbaService -ComputerName <ServerName> |
Select-Object ComputerName, ServiceName, State, StartMode |
Format-Table -AutoSizeReturns job execution history filtered by outcome. Useful for quickly surfacing recent failures:
Get-DbaAgentJobHistory -SqlInstance <ServerName> -Job '<JobName>' -OutcomeType Failed | Out-GridViewReturns enabled trace flags on an instance:
Get-DbaTraceFlag -SqlInstance <ServerName>Returns permissions for a database, optionally filtered by grantee. -IncludeServerLevel includes instance-level permissions alongside database permissions:
Get-DbaPermission -SqlInstance <ServerName> -Database <DatabaseName> `
-ExcludeSystemObjects -IncludeServerLevel |
Where-Object Grantee -eq '<RoleOrUserName>' |
Sort-Object Securable |
Select-Object PermState, PermissionName, SecurableType, Securable |
Out-GridViewReturns all logins on an instance. -PassThru allows selecting logins from the grid view and piping them to further commands:
Get-DbaLogin -SqlInstance <ServerName> | Out-GridView -PassThruStatus checks for replication and log shipping. These are read-only and safe to run against production.
Tests replication latency for a publisher instance. -DisplayTokenHistory shows the full tracer token history rather than just the latest:
Test-DbaRepLatency -SqlInstance <ServerName> -DisplayTokenHistoryExports the replication server configuration to a SQL script. Useful for documenting or recreating a publisher's setup:
Get-DbaRepServer -SqlInstance <ServerName> |
Export-DbaRepServerSettings -Path C:\temp\replication.sqlReturns log shipping status for an instance. -Simple condenses the output to the most relevant columns:
Test-DbaDbLogShipStatus -SqlInstance <ServerName> -SimpleReturns log shipping error history for an instance from a given date. -Secondary limits results to the secondary server role:
Get-DbaDbLogShipError -SqlInstance <ServerName> -DateTimeFrom "<MM/DD/YYYY>" -SecondaryReturns mirroring configuration and status for all mirrored databases on an instance:
Get-DbaDbMirror -SqlInstance <ServerName>Commands for generating database-level inventory reports. The custom format hashtables below produce human-readable size output and placeholder columns (Usage, Notes, SME) suitable for a handoff document or wiki table.
Requires the Format-Markdown module — see Install Maintenance Tools.
Returns all user databases on an instance with compatibility level, owner, and formatted size. Excludes system databases:
$fmtName = @{ label = "DBName"; Expression = { $_.Name } }
$fmtSize = @{ label = "Size"; Expression = {
if ($_.SizeMB -ge 1048567) { '{0:N2} TB' -f [math]::Round($_.SizeMB * 1MB / 1TB, 2) }
elseif($_.SizeMB -gt 1024 -and $_.SizeMB -lt 1048567) { '{0:N2} GB' -f [math]::Round($_.SizeMB * 1MB / 1GB, 2) }
else { '{0:N2} MB' -f [math]::Round($_.SizeMB, 2) }
}}
$fmtUsage = @{ label = "Usage"; Expression = { " " } }
$fmtNotes = @{ label = "Notes"; Expression = { " " } }
$fmtSME = @{ label = "SME"; Expression = { " " } }
Get-DbaDatabase -SqlInstance <ServerName> -ExcludeSystem |
Select-Object SqlInstance, $fmtName, Compatibility, Owner, $fmtSize, $fmtUsage, $fmtNotes, $fmtSME |
Sort-Object SqlInstance, DBName |
Format-MarkdownReturns the last index read and write times per database. Useful for identifying inactive or orphaned databases. Accepts a comma-separated instance list:
Get-DbaDatabase -SqlInstance <ServerName> -ExcludeSystem -IncludeLastUsed |
Select-Object ComputerName, Name, LastIndexRead, LastIndexWrite |
Sort-Object ComputerName, Name |
Format-Table -AutoSizeReturns fragmentation levels per index for a database. Pipe to Out-File to save as a report:
Get-DbaHelpIndex -SqlInstance <ServerName> -Database <DatabaseName> -IncludeFragmentation |
Select-Object SqlInstance, Database, Object, Index, IndexType, KeyColumns, IndexFragInPercent |
Format-Table -AutoSize |
Out-File -FilePath <OutputPath> -AppendTo scan all user databases on an instance, use -ExcludeDatabase to skip system and unwanted databases. -IncludeStats adds index statistics alongside fragmentation data:
Get-DbaHelpIndex -SqlInstance <ServerName> `
-ExcludeDatabase master, msdb, model, tempdb, <DatabaseName1>, <DatabaseName2> `
-IncludeFragmentation -IncludeStats |
Out-GridViewCopy-only full backup — safe for ad hoc backups as it does not break the existing backup chain:
Backup-DbaDatabase -SqlInstance <ServerName> -Database <DatabaseName> -Type Full -CopyOnly -Path <BackupPath>Simple restore, replacing the existing database if it exists:
Restore-DbaDatabase -SqlInstance <ServerName> -DatabaseName <DatabaseName> -Path <BackupFilePath> -WithReplaceRestore with explicit file mapping when the target server uses different drive paths than the source. -FileMapping takes a hashtable of logical name to physical path:
$FileStructure = @{
'<LogicalDataName>' = '<DataFilePath>'
'<LogicalLogName>' = '<LogFilePath>'
}
Restore-DbaDatabase -SqlInstance <ServerName> -DatabaseName <DatabaseName> -Path <BackupFilePath> `
-FileMapping $FileStructure -WithReplace `
-DestinationDataDirectory <DataDirectory> -DestinationLogDirectory <LogDirectory>Remaps or drops orphaned database users that no longer have a matching server login:
Repair-DbaDbOrphanUser -SqlInstance <ServerName> -Database <DatabaseName>Commands for copying objects between SQL Server instances.
Migrates all supported objects between instances in a single operation. Use -Exclude to skip object types that are handled separately or not needed on the destination:
Start-DbaMigration -Verbose -Source <SourceServer> -Destination <DestinationServer> `
-Exclude Databases, SpConfigure, CentralManagementServer, BackupDevices, Audits, `
ExtendedEvents, PolicyManagement, ResourceGovernor, ServerAuditSpecifications, `
DataCollector, Logins `
-DisableJobsOnDestinationCopies a SQL Server login from one instance to another, including SID and password hash:
Copy-DbaLogin -Source <SourceServer> -Destination <DestinationServer> -Login <LoginName>Copies a database between instances using backup/restore. -SharedPath must be accessible by both instances. -Force overwrites the destination if it exists:
Copy-DbaDatabase -Source <SourceServer> -Destination <DestinationServer> -Database <DatabaseName> `
-BackupRestore -SharedPath <UNCPath> -ForceCopies a SQL Agent job to another instance. -DisableOnDestination prevents the job from running immediately after copy:
Copy-DbaAgentJob -Source <SourceServer> -Destination <DestinationServer> -Job '<JobName>' -DisableOnDestinationCopies a linked server definition including credentials from one instance to another:
Copy-DbaLinkedServer -Source <SourceServer> -Destination <DestinationServer>