Getting Started with Dbatools

Getting Started with Dbatools

When I started using PowerShell with SQL Server I used a module from CodePlex named SQLPSX. The module provided quite a few functions that made working with SQL server easier. But today Codeplex is gone and I don't think anyone has updated SQLPSX in years. You can still get it on github but some of the folders have update dates like "3 years ago". Thank goodness for DBATools (https://dbatools.io/). I attended a pre-conferece session this year at SQL Pass Summit that was run by three people on  the team and their module does everything that I was using SQLPSX for and more. Click the link above and go to the download page for installation instructions then use this page a quick primer on some of the things you can use it for.

Getting Data In and Out of SQL

One of the things I use PowerShell for is to gather information on all my database servers and store that data in my central DBAAdmin database. Dbatools has a function, invoke-DbaSqlQuery, that can both read and write to the database. SQLPSX used two different functions to do this. In this first example I am going to use the function to get a list of all servers from my server table

import-module dbatools $server = 'DBAAdminDB-PRD' $db = 'DBAAdmin' $qry = 'select Server_name from dbo.vw_ServerListing' invoke-DbaSqlQuery -sqlinstance $server -database $db -query $qry

Very easy, no need to load .NET assemlies or create objects like you do when using SMO. The first line imports the module, then I create some variables and finally execute the statement. I could have done the whole thing in 2 lines by not using any variables.
Next lets use the function to insert some data into a SQL Server table. Suppose my server table only has three columns:  Server_name, Server_Env and Description

import-module dbatools $server = 'DBAAdminDB-PRD' $db = 'DBAAdmin' $qry = 'insert into dbo.server (Server_Name, Server_Env, Description)           VALUES ('SERVER1','PRD','Mission Critical Server')'  invoke-DbaSqlQuery -sqlinstance $server -database $db -query $qry

Again very simple. Now lets look at some more interesting things you can do with the module.

Getting Information about your Server and Databases

Here are a few commands you can use to get information about your SQL Server and it's Databases

import-module dbatools get-dbasqlinstanceproperty -SqlInstance TESTSERVER |select-object Name, Value

This command lists instance level information such as build number, edition, etc. You can see the output here

Now lets get some information on the databases on the server

import-module dbatools Get-DbaDatabase -SqlInstance TESTSERVER

Here is the output

And finally lets get some information about the datafiles of one of the databases.

import-module dbatools Get-DbaDatabaseFile -SqlInstance TESTSERVER -Database admin

These are just some very basic things you can do with Dbatools. There are over 300 commands in the module that you can use for gathering information, backing up SQL, or even for server migration. PowerShell is a very powerful language and is getting used more and more by SQL and Windows Admins. Using modules like dbatools is a great way to start using PowerShell easily and effectively