This post was first published for my employer, OneDigit AG, here.
dbatools really has the ability to change a DBAs work life. It is a free, very powerful and feature rich open source PowerShell module that offers you 550+ commands that allows you to take backups, restore them or just do a lot in SQL Server administration that you would need SSMS and some work for. With dbatools, you can do many tasks more and that with just a few lines of code. This post gives you an introduction to dbatools. dbatools is often called the SQL Server Management Studio (SSMS) for PowerShell. So in short, you can manage a SQL Server instance and its databases with PowerShell which also means that you can use scripts to manage them. Just to give some examples on how dbatools can be used so you’ll get a picture how helpful it can be.
# Simple query with a parameter which could be passed from a variable in the PowerShell code. It also prevents SQL injection.
Invoke-DbaQuery -SqlInstance "serverName" -Database "dbName" -Query 'SELECT * FROM Employees WHERE Name = @name' -SqlParameters @{ Name = "Marc Linder" }
# Another example with multiple parameters, which could be assigned from variables in PowerShell code before, too.
$queryBetween = @{
"StartDate" = $startdate;
"EndDate" = $enddate;
};
Invoke-DbaQuery -SqlInstance "serverName" -Database "dbName" -Query "StoredProcedure_with_in_between_search" -SqlParameters $QueryParameters -CommandType StoredProcedure
# Creates full backups of the two databases dbName1 and dbName2. If you don't specify any database names, it backups all databases on the instance!
# Backups will stored in the default backup folder. You can specify the target folder with -Path though. With -Type Diff you also could choose a differential backup
Backup-DbaDatabase -SqlInstance "ServerName" -Database dbName1, dbName2 -Path C:\backups\
Very simple, right? And there are 550+ commands like this in total that dbatools provides. Dbatools was invented by Chrissy LeMaire who is a MVP for Cloud and Datacenter Management & also MVP for Data Platform. She develops and maintains it now with Jess Pomfret (MVP Cloud and Datacenter Management) and Shawn Melton (MVP Cloud and Datacenter Management) and with many other contributors.
In my experience, dbatools does not only an awesome job for a lot of tasks around SQL Server but is also relatively easy and fast to develop with. It is ideal for almost any problem you may encounter as a DBA. dbatools will truly change your DBA life! With dbatools you don’t develop your hardest parts of the scripts for a DBA problem alone anymore as dbatools is open source (see code on github here) and thus tested within the community. A few lines of codes will boost your scripts that you would not be able to write alone.
If you’re worried about security, Chrissy also wrote a very insightful post on dbatools.io on how secure dbatools is – spoiler: its very secure.
So if you want to use dbatools, you can for example, just use SQL Server Job Agent which allows you to start your script according your needs, e.g. after a load or taking backups at a past midnight time very easily. Of course, you also can commit the code into a git repo and use it with SQL Server Job.
If you like to know more about dbatools, I recommend to buy and read the book “Learn dbatools in a Month of Lunches” by Chrissy LeMaire, Rob Sewell, Jess Pomfret and Cláudio Silva.
# How to install dbatools:
# Via PowerShell Gallery (Win 10+ / Win Server 2016+)
Install-Module dbatools
# Via Choclatey:
choco install dbatools -y
More install options are described here and there is also an option with downloading it if your server does not have any internet connection.
If you want to update dbatools, it of course also has a very neat tool to update itself:
Update-dbatools
If you need to choose the download option, it may happen that after you’ve installed a newer version of dbatools, you’ll end up having more than just one version of dbatools. In that case, you may want to remove older versions. To do that, you can use the following code as documented here:
& "$((Get-InstalledModule dbatools).InstalledLocation)\cleanup.ps1"
If you have any questions about dbatools, please feel free contact me. I’m happy to answer you!