dbatools is PowerShell module that you may think of like a command-line SQL Server Management Studio. The project initially started out as just Start-SqlMigration.ps1, but has now grown into a collection of over 500 commands that help automate SQL Server tasks and encourage best practices.
Want to contribute to the project? We'd love to have you! Visit our contributing.md for a jump start.
Want to say thanks? Click the star at the top of the page ?
Key links for reference:
- dbatools Slack channel for general discussion on the module and asking questions
- dbatools-dev Slack channel for discussion around contributing to the project
- dbatools documentation
Need an invite to the SQL Community Slack workspace? Check out the self-invite page. Drop by if you'd like to chat about dbatools or even join the team!
Installer
dbatools now works on PowerShell Core (aka PowerShell 6+). This means that you can run a large majority of our commands on Linux and macOS ??
Run the following to install dbatools from the PowerShell Gallery (to install on a server or for all users, remove the -Scope parameter and run in an elevated session):
Install-Module dbatools -Scope CurrentUser
If you don't have a version of PowerShell that supports the PowerShell Gallery, you can install it manually:
Invoke-Expression (Invoke-WebRequest https://dbatools.io/in)
Note: please only use
Invoke-Expression (Invoke-WebRequest..)from sources you trust, like us ?
Usage scenarios
In addition to the simple things you can do in SSMS (e.g. starting a job, backing up a database), we've also read a whole bunch of docs and came up with commands that do nifty things quickly.
- Lost sysadmin access and need to regain entry to your SQL Server? Use Reset-DbaAdmin.
- Need to easily test your backups? Use Test-DbaLastBackup.
- SPN management got you down? Use our suite of SPN commands to find which SPNs are missing and easily add them.
- Got so many databases you can't keep track? Congrats on your big ol' environment! Use Find-DbaDatabase to easily find your database.
Usage examples
As previously mentioned, dbatools now offers over 500 commands! Here are some of the ones we highlight at conferences.
PowerShell v3 and above required. (See below for important information about alternative logins and specifying SQL Server ports).
# Set some vars
$new = "localhost\sql2016"
$old = $instance = "localhost"
$allservers = $old, $new
# Alternatively, use Registered Servers
$allservers = Get-DbaCmsRegServer -SqlInstance $instance
# Need to restore a database? It can be as simple as this:
Restore-DbaDatabase -SqlInstance $instance -Path "C:\temp\AdventureWorks2012-Full Database Backup.bak"
# Use Ola Hallengren's backup script? We can restore an *ENTIRE INSTANCE* with just one line
Get-ChildItem -Directory \\workstation\backups\sql2012