SQL & PowerShell

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 friend!

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

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