dbatools is a very well known tool within the SQL Server community, and probably most of the production DBA’s are using it on daily basis.

But what about that IT guy whom main role is to take care of the company infrastructure not just SQL Server, and because there is no DBA around he does his best to fill the role as “accidental DBA” …

In case you are one of these accidental DBA’s that is looking an easy way to do “DBA” things in the most efficient and not too complex manner, let me tell you this: dbatools is what you have been looking for.

Let’s start defining what it is dbatools: It is a free and open source PowerShell module created by the SQL community with more than 500 SQL Server administration, best practice and migration commands included.

You can think of dbatools like a swiss army knife it has all the necessary things that you will make success and more!! To give you an idea of what kind of activities you can perform using dbatools, here you have a list of some of the most important categories:

• Availability groups
• Backup and restore
• Databases
• Endpoints
• File system and storage
• Log shipping
• Account management
• Mirroring
• Instance configuration
• Replication
• Network and connectivity
• Server management
• Migration

The migration commands are probably the most popular in the entire collection of dbatools, there is multiple testimonies (including myself) of DBA’s that are been very successful migrating multiple instances of SQL Server from one version to other just using dbatools. Here is a short video you can watch to convince yourself.

I know theory can be boring sometimes, so let’s look at what dbatools can do for us. Let’s use an availability group scenario as example, imagine you have two replicas and you need to keep in sync the logins created in the primary with the secondary most importantly for disaster purposes. This could be a very boring and repetitive task that we can easily automate with the help of dbatools.

I created a sandbox environment with an AG called “Legend”, where I have two replicas “Apollo” (primary) and “Adonis” (secondary). with the help of dbatools I will sync all the logins from “Apollo” to “Adonis” as follows:

PS C:\Users\master> Copy-DbaLogin -Source Apollo -Destination Adonis

Type                 Name                        Status     Notes
----                 ----                        ------     -----
Login - WindowsUser  MASTER\Apollo				 Successful
Login - WindowsUser  MASTER\Adonis				 Successful
Login - WindowsUser  MASTER\Duke				 Successful
Login - SqlLogin     Drago                       Successful
Login - WindowsUser  NT AUTHORITY\SYSTEM         Skipped    Local
Login - WindowsUser  NT SERVICE\ClusSvc          Skipped    Local
Login - WindowsUser  NT SERVICE\MSSQLSERVER      Skipped    Local
Login - WindowsUser  NT SERVICE\SQLSERVERAGENT   Skipped    Local
Login - WindowsUser  NT SERVICE\SQLTELEMETRY     Skipped    Local
Login - WindowsUser  NT SERVICE\SQLWriter        Skipped    Local
Login - WindowsUser  NT SERVICE\Winmgmt          Skipped    Local

Now, let’s imagine I also have to copy all the SQL Agent jobs from “Apollo” (primary) to”Adonis” (secondary), let’s do it:

PS C:\Users\master> Copy-DbaAgentJob -Source Apollo -Destination Adonis

Type      Name   							Status     Notes
----      ----   							------     -----
Agent Job DBA - Daily Backup 				Successful
Agent Job DBA - MSDB Maintenace				Successful
Agent Job DBA - Consistency Check			Successful
Agent Job DBA - Weekly Index Maintenance	Successful
Agent Job DBA - Error log monitoring		Successful

That’s it! A simply line of code using the Copy-DbaLogin and Copy-DbaAgentJob functions I was able to sync the logins and jobs between two replicas, the same process can be created on T-SQL but it will require a decent amount of effort and some investment of time creating the code to make this happen.

And this is just the tip of the Iceberg in case you want to learn more about dbatools, I encourage you to check their website. There is a section where you can check all the commands available, also a ton of videos in YouTube where you can learn more about this awesome tool!

Thanks for reading!