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.