DBATOOLS.IO….A DBA timesaver/luxury

The next few posts I am going to write about are about the awesome tool called DBATOOLS. It is an open source tool, created by Chrissy LeMaire and the SQL Server community and can be found here: https://dbatools.io/

POWERSHELL? Do not be afraid, I will show you how easy it is to install and some useful commands I use daily that are truly timesavers across my SQL Server universe.

There are over 500 commands included, but first you need to install the module.

First thing you need to do is open Powershell and run it as Administrator.

You will probably get an UAC warning asking if you want to continue, click OK or Yes.

Now Powershell will be opened. It looks like a DOS prompt.

If you have Windows 10, installation is easy. All you need to do is type in the following command:

Install-Module dbatools

You will then have the following notice to update some modules. I type Y and press enter.

After that you will get and Untrusted repository alert. I trust this repository so I press Y again.

It will now download the package: dbatools.

When it is done installing, it will go back to the default prompt.

To verify that the module got installed run this command, it will show all installed modules on your machine. I am currently running version 1.1.6.

Get-Module -ListAvailable

If you have trouble getting it installed, the site has a few other ways to install.

https://dbatools.io/download/ or https://dbatools.io/offline/

Now dbatools is installed. What can you do with it? Over 500 different things, but here is a useful command to get us started: Get-DbaDatabase.

I am using Windows Authentication, so I do not have to pass any credentials. You do need to pass in a SqlInstance, so I put in one of my servers named: ETLSQLDB.

Get-DbaDatabase -SqlInstance etlsqldb

What you get is a list of all of the databases on the server and their last backup times, database size, among other information.

Having a list in this format is not that helpful, so you can use the Powershell pipe command ” | out-gridview” to list it into a handy table.

Get-DbaDatabase -SqlInstance etlsqldb |out-gridview

Now you get an easy to read pop-up with all of the same information. Yes, you can copy and paste this information out and put it into Excel.

Ok, so how does this save you time? You only get one servers worth of information. Not true!! Watch and see!!! You can pass in a variable with multiple server names and it will list all of the servers information.

You declare a variable with a leading $ sign. So I am going to create a variable called $ServerList, and I will include 4 different servernames. You can also put this variable in your Powershell profile so it is always available, but that is beyond the scope of this article.

$ServerList = "AMRDBARC", "AMRDBPROD", "SFTPSQL", "ETLSQLDB"

Now watch when I call Get-DbaDatabase but pass in the $ServerList variable, instead of the individual server name.

Now all of the servers and their databases show up in an easy to read form!

For more information and other options for this command, please follow this link: https://docs.dbatools.io/#Get-DbaDatabase

The main site is at this location: https://dbatools.io/