SQL Server and PowerShell Automation – DBATools

By Rohit Khadka, Sr. Database Administrator

Many of us – Production DBAs, at some of our career
have had to work with PowerShell to perform operations outside of SQL Server.
Sometimes, this task can be a bit intimidating considering that it wasn’t
really part of the job description (let’s just admit that we didn’t read
through all the job requirements 😊).
PowerShell has become a more integral part of DBA work as automation has taken
over manual steps. Less room for human error and more flexibility around how
things can be done.

Today, we are going to discuss about a tool which
is probably one of the most powerful open-source DBA tools available out there
–DBATools. This tool has just been around for a few years, but the community
contribution has been tremendous over that time period. The tool is easy to
install, and the best part is it’s completely – free. It supports over 500 SQL
Server administration, best practice and migration commands. And, the community
is still growing with over 140 contributors and over 1700 users in Slack
channel who are actively involved. [1]

Personally, I have used this tool to perform different kinds of migration operations across multiple servers. It saved me a lot of time without having to script out everything and then copying and running those scripts on other servers. I was impressed and, I was happy that I could utilize my time doing other things that were less monotonous. With so many commands out there, I feel this tool has become such a powerful resource for DBAs by providing all-in-one functionalities. Without much further ado, let’s dive into how we can get started. In order to install DBATools, you will first need to run the PowerShell as administrator by right click on it and click ‘Run as Administrator’. Run the command: ‘install-module DBATools’.

Next, a pop-up screen shows up after running the script above, which looks something like this:

You can click ‘Yes’ and you should get this prompt screen below to which you can select ‘Yes to All’. And, then let the magic begin!

To verify installation, you can just run the command ‘Get-Module -ListAvailable DBATools’ like this:

Once the installation
is complete. You are all set to begin your new and better DBA life without the
hassle of scripting out things anymore. PowerShell is going to be your new best

Here is the list of all commands that you can run using your new DBAtools: https://dbatools.io/commands/

[1] https://dbatools.io/commands/