First, download and install
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!
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.
Approach to learning
dbatools now offers over 400 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).
Alternative SQL Credentials
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.
Get-DbaDatabase -SqlInstance sql2017 -SqlCredential (Get-Credential sqladmin)
A few (or maybe just one – Restore-DbaDatabase), you can also use
Alternative Windows Credentials
For commands that access Windows such as Get-DbaDiskSpace, you will pass the
To store credentials to disk, please read more at Jaap Brasser’s blog.
Servers with custom ports
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.
dbatools aims to support as many configurations as possible, including
- SQL Server 2000 – 2017
- Express – Datacenter Edition
- Clustered and stand-alone instances
- Windows and SQL authentication
- Default and named instances
- Multiple instances on one server
- Auto-populated parameters for command-line completion (think -Database and -Login)
Want to know more? Our blog has a lot of great articles. Here are some of the ones that focus on functionality: