dataplat/dbatoolsPublic

NotificationsYou must be signed in to change notification settings
Fork847
Star2.7k

🚀 SQL Server automation and instance migrations have never been safer, faster or freer

License

MIT license

2.7k stars 847 forks Branches Tags Activity

Star

Notifications

You must be signed in to change notification settings

Branches Tags

Folders and files

Name		Name	Last commit message	Last commit date
Latest commit History 17,526 Commits
.devcontainer		.devcontainer
.github		.github
.vscode		.vscode
bin		bin
en-us		en-us
opt		opt
private		private
public		public
tests		tests
xml		xml
.gitattributes		.gitattributes
.gitignore		.gitignore
.markdownlint.json		.markdownlint.json
CONTRIBUTING.md		CONTRIBUTING.md
appveyor.yml		appveyor.yml
cleanup.ps1		cleanup.ps1
code_of_conduct.md		code_of_conduct.md
codecov.yml		codecov.yml
dbatools.psd1		dbatools.psd1
dbatools.psm1		dbatools.psm1
license		license
readme.md		readme.md

Repository files navigation

dbatools

Migrate SQL Server instances in minutes instead of days. Test hundreds of backups automatically. Find that one database across 50 servers. dbatools is a PowerShell module with nearly 700 commands that replace manual SQL Server administration with powerful and fun automation.

Performance at Scale: Migrate terabyte databases in under an hour. Test 1000+ backups per hour. Manage 100+ SQL instances from a single console.

Why dbatools?

Traditional Methods	dbatools
SSMS: Click through 50 servers manually	PowerShell: Query all 50 servers in one command
Migration: Days of planning and execution	Migration: Minutes with automated best practices
Backup Testing: Manual restores, hope for the best	Backup Testing: Automated verification of all backups
Documentation: Hours of manual collection	Documentation: Instant HTML/Excel reports
Scripting: Complex T-SQL across versions	Scripting: Consistent commands for SQL 2000-2022

Quick Start

# Check your PowerShell version (v3+ required for Windows, Core 7.4+ for Linux/macOS)$PSVersionTable.PSVersion# Install (Windows/Linux/macOS)Install-Module dbatools-Scope CurrentUser# See your databasesGet-DbaDatabase-SqlInstance localhost# Check your backupsGet-DbaLastBackup-SqlInstance localhost|Format-Table# Test your last backup (yes, really!)Test-DbaLastBackup-SqlInstance localhost

System Requirements

SQL Server Support

Version	Commands Supported
SQL Server 2000	75%
SQL Server 2005	90%
SQL Server 2008/R2	93%
SQL Server 2012+	100%
Azure SQL VM	As per version above
Azure SQL Database	40%
Azure SQL Managed Instance	60%
Containers/Kubernetes	75%

Operating System Support

OS	Commands Supported	PowerShell Required
Windows 7/8/10/11	100%	v3+
Windows Server 2008 R2+	100%	v3+
Linux (Intel/ARM64)	78%	Core 7.4.0+
macOS (Intel/M1)	78%	Core 7.4.0+

💡Note: Commands requiring SQL WMI or-ComputerName parameter typically don't work on Linux/macOS.

Network Requirements

For remote SQL Server management, ensure these ports are accessible:

Protocol	Default Port	Used By	Required For	Firewall Note
SQL Database Engine	1433	`Get-DbaDatabase`	62% of commands	Allow inbound on SQL Server
WS-Management	5985/5986	`New-DbaClientAlias`	25% of commands	Windows Remote Management
SQL WMI	135	`Enable-DbaAgHadr`	4% of commands	DCOM/RPC endpoint mapper
SMB	445	`Backup-DbaDatabase`	4% of commands	File sharing for backups

Firewall Tip: Create a dedicated Windows Firewall rule group for dbatools management traffic.

Common Use Cases

Backups & Restores

# Backup all databasesGet-DbaDatabase-SqlInstance sql01|Backup-DbaDatabase# Simple restoreRestore-DbaDatabase-SqlInstance sql01-Path"C:\temp\mydb.bak"# Test ALL your backups on a different serverTest-DbaLastBackup-SqlInstance sql01-Destination sql02|Out-GridView

Migrations

# Migrate entire SQL instance with one command$params=@{Source='sql01'Destination='sql02'BackupRestore=$trueSharedPath='\\nas\temp'}Start-DbaMigration@params-Force# Copy jobs between serversCopy-DbaAgentJob-Source sql01-Destination sql02

Monitoring & Health

# Find databases without recent backupsGet-DbaLastBackup-SqlInstance sql01|Where-Object LastFullBackup-lt (Get-Date).AddDays(-7)# Check for corruptionGet-DbaLastGoodCheckDb-SqlInstance sql01|Out-GridView# Monitor currently running queriesInstall-DbaWhoIsActive-SqlInstance sql01-Database masterInvoke-DbaWhoIsActive-SqlInstance sql01

Finding & Discovery

# Find databases across multiple serversFind-DbaDatabase-SqlInstance sql01, sql02, sql03-Pattern"Production"# Find stored procedures containing specific textFind-DbaStoredProcedure-SqlInstance sql01-Pattern"INSERT INTO Audit"# Discover SQL instances on networkFind-DbaInstance-ComputerName server01, server02

Installation

Prerequisites

# Check your PowerShell version$PSVersionTable.PSVersion# Set execution policy (one-time setup)Set-ExecutionPolicy-ExecutionPolicy RemoteSigned-Scope CurrentUser# Trust PowerShell Gallery (one-time setup)Set-PSRepository-Name PSGallery-InstallationPolicy Trusted

Install Methods

For Current User (Recommended)

Install-Module dbatools-Scope CurrentUser

For All Users (Requires Admin)

Install-Module dbatools

Offline Installation

# On internet-connected machine:Save-Module-Name dbatools-Path C:\temp# Copy to target machine and place in:# - All users: C:\Program Files\WindowsPowerShell\Modules# - Current user: $HOME\Documents\WindowsPowerShell\Modules# Import the module after copyingImport-Module dbatools

⚠️ Certificate Change Notice (v2.5.5+)

Starting with v2.5.5, dbatools uses Microsoft Azure Trusted Signing. When upgrading from older versions:

Install-Module dbatools-Force-SkipPublisherCheck

Full migration guide →

Getting Help

# Detailed help for any commandGet-HelpTest-DbaLastBackup-Full# Find commandsGet-Command-Module dbatools*backup*Find-DbaCommand-Tag Migration# Online helpGet-HelpTest-DbaLastBackup-Online

Resources:

Advanced Usage

Authentication

SQL Authentication

$cred=Get-Credential sqladminGet-DbaDatabase-SqlInstance sql01-SqlCredential$cred

Alternative Windows Credentials

$cred=Get-Credential ad\winadminGet-DbaDiskSpace-ComputerName sql01-Credential$cred

Custom Ports

# Using colon or comma for non-default portsGet-DbaDatabase-SqlInstance'sql01:55559'Get-DbaDatabase-SqlInstance'sql01,55559'# Note: quotes required

PowerShell Transcript

# Import module before starting transcript (PS 5.1 requirement)Import-Module dbatoolsStart-TranscriptGet-DbaDatabase-SqlInstance sql01Stop-Transcript

Troubleshooting

Common Issues

Issue: "Could not connect to SqlInstance"

# Test connectivityTest-DbaConnection-SqlInstance sql01# Check if SQL Browser service is running for named instancesGet-DbaService-ComputerName sql01-Type Browser

Issue: "Access denied" errors

# Ensure you have proper SQL permissionsGet-DbaLogin-SqlInstance sql01-Login$env:USERNAME# For Windows authentication issues, verify domain connectivityTest-ComputerSecureChannel

Issue: Module won't import

# Check execution policyGet-ExecutionPolicy# Force reimport if neededRemove-Module dbatools-Force-ErrorAction SilentlyContinueImport-Module dbatools-Force

For more troubleshooting help, visit ourtroubleshooting guide or ask inSlack.