Being efficient is an integral part of being an effective DBA. In this post, I will introduce you to a tool that can significantly improve your work as SQL DBA -dabtools.

Dbatools is an open-source PowerShell module for managing SQL Server. Started by Chrissy LeMaire(B|T) back in 2014 as a project to migrate SharePoint SQL Server instances, nowadays the module comes with almost 600 commands for automating SQL Server from the command line.  One of the things I love most about dbatools is that the module is community-driven and there are more than 190 contributors- if you get stuck or have any questions, the amazing folks from the community are always happy to help.

1. Installing Dbatools

Getting started with dbatools is quick and easy. First, you need to install the module- open PowerShell as Administrator and run the following command:

Install-Module dbatools 

If you don’t have administrative rights on the machine or you want to instal the module only for your current user you can use:

Install-Module dbatools -Scope CurrentUser 

Or if you don’t have internet access, you can download the module and then copy it to the machine with no internet. More information about offline install is available on dbatools.io

2. Sp_Configure with dbatools

Almost every DBA uses sp_configure to view server settings or to make config changes – thanks to dbatools we can use sp_configure capabilities via the command line across multiple servers simultaneously.

To get information about server-level system configuration you can use Get-DbaSpConfigure. The command will return all system configuration information. You can also add parameters to obtain a narrow view, for example specifying –Name will return only that specific configuration.

#Get all Configuration properties
Get-DbaSpConfigure -SqlInstance $Instance1 
 
#Get the value for XPCmdShellEnabled
Get-DbaSpConfigure -SqlInstance $Instance1 -Name XPCmdShellEnabled

The Set-DbaSpConfigure command helps you make changes to the server level system configuration.

#Set the value for XPCmdShellEnabled
Set-DbaSpConfigure -SqlInstance $Instance1 -Name XPCmdShellEnabled -Value 1

Once you have made some configuration changes you may want to export the advanced sp_configure global configuration to a SQL file:

#Export the configuration to a .sql file (useful for documentation purposes)
Export-DbaSpConfigure  -SqlInstance $Instance1 -Path C:\temp

The real power of PowerShell is that you can work with multiple instances. Here is an example of how you can compare the configuration information of two instances:

#Compare configurations of two servers 
$SPConfigure_Instance1 = Get-DbaSpConfigure -SqlInstance $Instance1
$SPConfigure_Instance2 = Get-DbaSpConfigure -SqlInstance $Instance2
$propcompare = foreach ($prop in $SPConfigure_Instance1) {
    [pscustomobject]@{
        Config                  = $prop.DisplayName
        'Windows Node1 setting' = $prop.RunningValue
        'Windows Node2 Setting' = $SPConfigure_Instance2 | Where DisplayName -eq $prop.DisplayName | Select -ExpandProperty RunningValue
    }
}

Copying sp_configure settings from one instance to another – something easily achievable with dbatools:

#Copy configuration from one Instance to the other
Import-DbaSpConfigure -Source $Instance1 -Destination $Instance2

You can also see and change the  sp_configure settings of multiple servers simultaneously.

#Configure multiple Instances simultaneously
$AllInstances = $Instance1 , $Instance2
Get-DbaSpConfigure -SqlInstance $AllInstances -Name XPCmdShellEnabled
Set-DbaSpConfigure -SqlInstance $AllInstances -Name XPCmdShellEnabled -Value 0

3. Backups with Dbatools

You are only as good as your last backup, so making regular backups is a keystone of any business continuity and disaster recovery strategy. Backups are among the most performed tasks by every DBA- luckily dbatools can help you to quickly and easily perform and test backups.

The command for backing up one or multiple databases is Backup-DbaDatabase. If you want to do a full backup on all databases on an instance and place the backup files to a certain location, you can run:

#Full backup of all databases
Backup-DbaDatabase -SqlInstance $Instance1 -Path C:\temp\Backup -Type Full

To check how long it will take to complete the backup use Measure-DbaBackupThroughput. The command uses the information stored in MSDB and returns minimum and maximum throughput, average backup duration, first and last backup date, database backups count and average backup size.

Measure-DbaBackupThroughput -SqlInstance $Instance1

Get-DbaBackuphistory is used to get details about all backups belonging to a particular database or instance in SQL Server with minimal effort. The output includes the backup type, size, backup device type duration, start and end time. 

#Backup history details for all databases on Instance1:
Get-DbaDbBackupHistory -SqlInstance $Instance1

Before restoring a database, it is a good practice to check that we have enough space on the target instance. Once we know we have the required space we can perform the restore. Here is an example of how you can do this with dbatools:

#Check we have enough space to restore AdventureWorks2019 from Instancce1 to Instance2
Measure-DbaDiskSpaceRequirement -Source $Instance1 -Destination $Instance2 -Database AdventureWorks2019

#Restore databases on Instance2 from file location
Restore-DbaDatabase -SqlInstance $Instance2 -Path C:\temp\Backup -WithReplace

Having a backup that cannot be restored is like having lifeboats that don’t float- so testing your backup is essential. With dbatools you can do this easily – Test-DbaLastBackup tests the last set of full backups in a SQL instance via performing the following steps:

• It gathers the last full backup information for the specified database. If we do not specify a particular database, it gathers information for all databases
• It restores the last full backup of a database with a new name in the specified server. If we do not specify any instance, it restores database SQL Server on the source instance. By default, the new database name is in the format of dbatools-testrestore-$databaseName. We can specify a custom database name as well
• It also renames the logical and physical file of a new database. It ensures no conflict with database filenames
• It performs DBCC CHECKTABLE to Checks the integrity of all the pages and structures for all tables in a new database
• In the last step, it drops the newly restored database

Test-DbaLastBackup -SqlInstance $Instance2

4. Logins with Dbatools

Ensuring that users and applications can successfully connect to the databases they require is essential – with dbatools you can simplify the work that is required to administer instance logins and database users.

You can create new SQL /or Windows authentication/ login. You can also check all logins that already exist on an instance.

#Returns info for all the logins from Instance1 using NT authentication
Get-DbaLogin -SqlInstance $Instance1
 
#Create a new SQL login named Lina1 on Instance1
New-DbaLogin -SqlInstance $Instance1 -Login Lina1

You may also want to get the users of a Database and then add a new user. 

#Gets the users for AdventureWorks2019 
Get-DbaDbUser -SqlInstance $Instance1 -Database AdventureWorks2019 | Format-Table

#New sql user with login named Lina1 in AdventureWorks2019
New-DbaDbUser -SqlInstance $Instance1 -Database AdventureWorks2019 -Login Lina1

In addition, you can check the members’ roles and easily assign a role to a user.

#Returns all members roles of AdventureWorks2019 on Instance1
Get-DbaDbRoleMember -SqlInstance $Instance1 -Database AdventureWorks2019

#Add user Lina1 to the roles db_datareader and db_datawriter in AdventureWorks2019
Add-DbaDbRoleMember -SqlInstance $Instance1 -Database AdventureWorks2019 -Role db_datareader, db_datawriter  -User Lina1

Covering all 500+ SQL Server best practices, administration, development and migration commands included in dbatools is a mission impossible, but I strongly recommend checking the module if you are looking for automation or an easier and quicker way to perform tasks. Although I am still new to dbatools, I was lucky to be introduced to the module by my amazing colleagues; I am currently using it daily and it has helped me significantly improve my efficiency. This blog was a short summary of just a few of the many brilliant things you can do with the module- I strongly suggest checking their website and exploring it further. I would love to hear what you think of dbatools – please share your experience in the comments below.