Automate your SQL Server tasks using DBATools (PowerShell)

What is dbatools and PowerShell?

Dbatools is an open-source module of PowerShell developed by big names from the SQL server community, MVPs, and other SQL server and PowerShell professionals. The dbatools uses SMO (server management objects) from the .Net framework, the same objects used bu SSMS (SQL Server management studio).

With SMO objects, we can manipulate any SQL Server object using the C# program language. In addition to SMO’s widespread use,  dbatools use the WMI/CIM (Windows Management Instrumentation/Common Information Model) to manipulate windows server objects.

With all this power, it is focused on the administrative tasks of SQL Server environments, implementing good practices for development, migration, and daily routines. Dbatools.io currently has more than 500 commands to assist you in your daily activities.

How to install dbatools?

There is only a requirement: PowerShell v3+. Once you meet these requirements, installation is simple:

• Open PowerShell command window with Administrator rights (Right Click on Window and select run as an administrator);
• Run the following command:
  • Install-Module dbatools;
    

    

• To give affirmative answers to the questions type Y (Yes) in the prompt.
• That’s it! You are done.

You can also use the offline method to install in your server with you don’t have internet access.  First, download the package on https://dbatools.io/zip and copy it preferably to some path that is configured in your environment variable $ env: PSModulePath.

• Open the Start Search, type in “env”, and choose “Edit the system environment variables”.
• Click the “Environment Variables…” button

  

• After copying the package to one of the above locations, you can install Dbatools using the command:
  • Import-Module -Name Dbatools

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

How to check if dbatools was correctly installed?

One way is to use the Get-Module command presented below.

• Get-Module dbatools -ListAvailable
  

  

If you don’t receive that message, it may mean that the module is not installed on the machine or has not been loaded into the memory yet.

Getting started with dbatools

Let’s navigate through Powershell using the native cmdlets. Below are some commands that you will use in your everyday life.

• Get-Command – List all package commands installed in the machine.
• Get-Help – Get command details.
• Get-command -Module dbatools – List all commands from dbatools module.
• Get-command *Get-DbaSQL* – To filter the search, use the asterisk (*) wildcard character with the Name parameter.
• Get-Help Get-DbaSqlService -Examples – The Examples parameter displays the help file’s NAME and SYNOPSIS sections and all the Examples, as you can see below:
  
• Get-Help Get-DbaSqlService -Detailed – The Detailed parameter displays the help article’s detailed view that includes parameter descriptions and example, as you can see below:
  

The better your knowledge of Powershell, the easier it will be to work with dbatools, and the easier it will be to extract rich information.

More information

The dbatools commands automate and simplify many of the tasks we’ve all had to do multiple times. Want to know more? Here are some great articles that focus on functionality:

• Getting complex with Restore-DbaDatabase;
• Scheduling PowerShell Tasks with SQL Server Agent;
• Building a dedicated backup test server;
• Scheduling a migration;
• Dealing with SPNs.

Conclusion

PowerShell is everywhere. Learning PowerShell prepares you for the inevitable (and fun!) automation-rich future. Once you learn how to use an automation tool like PowerShell, you can easily transition your skills to automate everything from on-premise Instances (Windows/Linux) to the Azure SQL database (it even helps power the cloud).

Elevate your SQL Servers with our managed services, performance tuning, and consulting services, tailored for companies seeking optimal database solutions.