Joey on SQL Server

Getting Started with Dbatools for SQL Server

An open source project gives DBAs the ability to channel PowerShell for automating and scaling complex SQL Server tasks, starting with the most basic: database backups.

• By Joey D'Antoni
• 05/13/2020

I've been working with SQL Server for over 20 years now, Generally, I limit my tools to the things I might expect to find in a given client environment. My toolbox typically includes the tools that Microsoft ships to manage SQL Server, like SQL Server Management Studio and Azure Data Studio, Windows Performance Monitor (perfmon) and a selection of curated T-SQL scripts that can help me analyze the performance of a server or database.

While I have used PowerShell extensively in working with the Windows operating system and with Azure, my interactions with SQL Server had been limited, mainly because of the complexities of the system management objects (SMO) that lie beneath the native SQL Server PowerShell (SQLPS) cmdlets.

The SMO is a collection of .NET objects that are designed for programming against SQL Server. In the early days of the SQLPS module, using PowerShell against SQL Server required development skills beyond what most DBAs and sysadmins possessed. Among the data platform MVP community, there were a few folks who really understood PowerShell and used it for a lot of tasks, but most people were turned off by the inherent complexity. 

A group of enterprising DBAs led by Chrissy LeMaire in Belgium and Rob Sewell in the United Kingdom realized the capabilities that PowerShell provided for automating and scaling complex tasks, as well as the usability challenges of the native solutions, and started down the path of building an open source project called dbatools.

LeMaire started dbatools in 2014 as a project to migrate her SharePoint SQL Server instances. Out of that, the project has grown to accepting community contributions in 2016, to a version 1.0 release in 2019. The project now has over 160 contributors and 550 commands.

Now that we've had a bit of a history lesson, what can it do?

dbatools is a designed to be a tool for end users -- in this case, DBAs, not developers. The commands are broken down into a number of categories, starting with one of the most basic operations a DBA does: backup their databases. Backing up all of your databases on a server is as simple is this one line of code:

Backup-DbaDatabase  -SqlInstance sql1 -Path \\backups\sql1\ -FileCount 8 `
-CompressBackup 'yes' -Verify -Type Full

That command will back up all of the databases on your instance to (in this case) \\backups\sql1 while optionally creating subfolders for each database. There are other tools that offer similar functionality. However, the magic of the backup commands is that you can easily restore all of those databases with a similar single line of code.

Do you have additional differential and transaction log backups? That's not a problem, as the restore-dbadatabase command will scan the folder and the headers of each backup file to generate a restore script to be executed on the service, and it offers the option of just outputting the T-SQL statements required to execute the restore process manually.

In addition to backup and restore, there is a command to test your last backup called test-dbalastbackup. Testing the restore process is something that should be done regularly, but it does not happen as frequently as it should. This command automates the process of doing that testing and restores the backup to a remote (or local) server. In addition to doing the restore, it performs a consistency check on the newly restored database, which ensures the full validity of the backup.

While dbatools covers much of the surface area of SQL Server, the other place where it really shines is migration and server configuration. Migrating user databases has never been that challenging, but the process of setting up transaction log shipping or mirroring represents a significant effort toward restoring databases on the target server. Building on the strength of the backup and restore process that is defined, dbatools has commands to automatically configure database mirroring and log shipping, requiring minimal manual intervention from the DBA.

Another challenge of doing server migrations is moving the SQL Server Agent jobs and log-ins from one server to another. I've used SQL Server Integration Services to do this in the past and, at best, it was a messy, ugly process. dbatools allows you to migrate each element of the server's settings and metadata in a very granular fashion. For example, if you just wanted to move your SQL Server Agent jobs but not your alerts, you have that level of control.

Typically, when performing a server migration, you want to bring over everything at once. Using the Copy-DBAAgentServer command, you can bring over everything in the SQL Server Agent, including jobs, notifications, operators and alerts. Log-ins work in the same easy manner. A couple of months ago, while my team was at a customer repairing a development server, we lost the master database and didn't have a backup handy. We were able to quickly copy all of the log-ins from the production database using the copy-dbalogin command.

Automating your regular processes has a number of benefits. It frees you from doing tedious, repetitive tasks. It also provides better quality across your environment because you are far less likely to have misconfigurations that cause problems. And if you do, you have an automated framework to check them (that happens to be a dbatools side project called dbachecks that you should look into) and easily fix configuration issues.

Even if you don't build a large automation pipeline, having an easier process for backups and restores and for moving users from one server to another can benefit even the smallest SQL Server shop.