dbatools is a free PowerShell module with over 500 SQL Server administration, best practice and migration commands included.


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!

Looking for more in-depth information? Read Installing modules from the PowerShell Gallery and Offline installs of dbatools.

Usage scenarios

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 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.

Usage examples

Here are some of the commands we highlight at conferences. (See below for important information about alternative logins and specifying SQL Server ports).

Important Note

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 sqladmin

A few (or maybe just one – Restore-DbaDatabase), you can also use -AzureCredential.

Alternative Windows Credentials

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.

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.

Support

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)

More information

Want to know more? Our blog has a lot of great articles. Here are some of the ones that focus on functionality:


 

Related commands