dbatools is a free PowerShell module with over 500 SQL Server administration, best practice and migration commands included.
To get started, first download the dbatools module. Multiple download/install options are available, including installing from the PowerShell Gallery, GitHub and chocolatey. PowerShell v3+ is the only requirement – Microsoft allowed us to include the required SMO libraries in our project!
Looking for more in-depth information? Read Installing modules from the PowerShell Gallery and Offline installs of dbatools.
Ultimately, you can think of dbatools as a command-line SQL Server Management Studio. But in addition to the simple things you can do in SSMS (like starting a job), 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.
dbatools now offers over 500 commands! That number 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 below that will help you get started.
Here are some of the commands we highlight at conferences. (See below for important information about alternative logins and specifying SQL Server ports).
By default, all SQL-based commands will login to SQL Server using Trusted/Windows Authentication. To use alternative credentials, including SQL Logins or alternative Windows credentials, use the -SqlCredential. This parameter accepts the results of Get-Credential which generates a PSCredential object.
A few (or maybe just one – Restore-DbaDatabase), you can also use -AzureCredential.
For commands that access Windows such as Get-DbaDiskSpace, you will pass the -Credential parameter.
To store credentials to disk, please read more at Jaap Brasser’s blog.
If you use non-default ports and SQL Browser is disabled, you can access servers using a semicolon (functionality we’ve added) or a comma (the way Microsoft does it).
Note that PowerShell sees commas as arrays, so you must surround the host name with quotes.