ARTICLE

A Gentle Introduction to dbatools Commands

From Learn dbatools in a Month of Lunches by Chrissy LeMaire, Rob Sewell, Jess Pomfret, Cláudio Silva

20 min readMar 23, 2022

In this article you’ll learn about a few commands and four of the common dbatools parameters: SqlInstance, SqlCredential, ComputerName and Credential.

Take 40% off Learn dbatools in a Month of Lunches by entering fcclemaire into the discount code box at checkout at manning.com.

These parameters are particularly useful because they’re used in nearly every command to connect to both local and remote servers. The primary goal of this article is to get you comfortable with these common parameters. Having consistent parameters throughout the entire module was a high priority for the 1.0 release of dbatools.

Getting started

As DBAs it’s in our nature to be wary; we want to understand what a tool is going to do before we let it anywhere near our production environment. For this reason, we’ll start with commands that are read-only.

The first command is one that helps you to check if you can connect to the SQL Server instances. We figure that this was a good place to start because if you can’t connect to the SQL Server instance, then you can’t use any of the dbatools commands.

We’ll also show you how to list the services on the host for SQL Server and how to list the databases on an instance using dbatools. We chose these as our starting point as they are common scenarios that don’t perform any changes.

Checking the SQL connection

dbatools, like SQL Server Management Studio (SSMS) and any PowerShell command that you run against a SQL Server, can only accomplish what’s available to the user account which is running the PowerShell command.

No magic is involved here; at both the operating system level and the SQL Server level, you can only perform the actions which your user account has permission.

Before we start doing anything, it’s a good idea to check that the user account which is running the PowerShell process can connect to the SQL Server instance and that the SQL Server instance is running before you start running other commands. This is a bit like using the connect dialogue in SSMS.

Figure 1. SSMS’s familiar connection dialog box

When this connects, you know at the least that SQL Server is running on that instance and that the account being used has CONNECT permissions.

Now we translate that into a dbatools command. Right now, we’re using this command to teach you some of the common dbatools parameters. As you progress with your dbatools and PowerShell learning, you’ll want to use this command to check if you’ve a working connection prior to running any further commands to get results which aren’t full of failed to connect errors.

The dbatools command that you use to accomplish a test connection is appropriately named Connect-DbaInstance. We’ll also explore Test-DbaConnection, which not only connects to the database engine, but performs a few other tests.

First, getting help

In our experience, some PowerShell users may not know that help is available not only on StackOverflow or in Slack, but within PowerShell as well. Because of this, we’ll touch on Get-Help.

You can use the Get-Help command to find out how to use any PowerShell command and we recommend that you remember to use Get-Help every time you want to use a PowerShell command which is new to you.

Although we won’t show the Get-Help example for every command, it’s still a good idea to use it for each new command that you run. Even though we’ve been using PowerShell for years, Get-Help is our go-to command anytime we run a new command. Get-Help -Examples is a particular favorite.

Let’s find out how to use Get-Help and Test-DbaConnection together.

Listing 1. Getting help for Test-DbaConnection

PS C:\> Get-Help Test-DbaConnection –Detailed

When you run the command in Listing 1, you can see output similar to the text in Figure 2.

In the Synopsis you can see that this command is for Testing the SQL Server connection. The Parameters section shows the parameters available to this command.

dbatools, like SSMS, should be installed on as few servers as possible. This is to avoid performance impacts, reduce your attack surface, and increase maintainability.

Logging into remote servers via remote desktop (RDP) is considered unsafe by security professionals. Not only is it more convenient to connect remotely to servers, it’s more secure. dbatools enables you to easily manage your entire estate from a centralized location.

Now, run your first dbatools command

Now we’re going to test the connection to your local SQL Server instance using Test-DbaConnection. This command also checks the connection for PowerShell remoting, which helps run commands targeted at the operating system such as Get-DbaDiskSpace.

Listing 2. Test SQL Engine and PowerShell remoting connectivity to a local instance

PS C:\> Test-DbaConnection -SqlInstance $Env:ComputerName

Note that when our commands reference $Env:ComputerName or localhost, it’s expected that each of these commands runs against a test instance on localhost.

$Env:ComputerName is a PowerShell default environment variable containing the name of the current machine.

Now you can see output similar to the output in Listing 3. If you’ve a successful connection with the account running PowerShell to your local instance then the ConnectSuccess property is true.

Notice that the output returns much more than whether there was a successful connection.

Listing 3. Example output of Test-DbaConnection

ComputerName         : DEVSQL                 ❶
 InstanceName         : MSSQLSERVER            ❷
 SqlInstance          : DEVSQL                 ❸
 SqlVersion           : 14.0.2002              ❹
 ConnectingAsUser     : AD\wdurkin             ❺
 ConnectSuccess       : True                   ❻
 AuthType             : Windows Authentication ❼
 AuthScheme           : NTLM                   ❽
 TcpPort              : 1433                   ❾
 IPAddress            : 172.16.11.162          ❿
 NetBiosName          : DEVSQL                 ⓫
 IsPingable           : True                   ⓬
 PSRemotingAccessible : True                   ⓭
 DomainName           : AD.local               ⓮
 LocalWindows         : 10.0.17134.0           ⓯
 LocalPowerShell      : 6.1.0                  ⓰
 LocalCLR             :                        ⓱
 LocalSMOVersion      : 15.1.18068.0           ⓲
 LocalDomainUser      : True                   ⓳
 LocalRunAsAdmin      : True                   ⓴
 LocalEdition         : Core                   ❷❶

❶ The Windows name of the machine

❷ The SQL Server instance — MSSQLSERVER is a default instance

❸ The machine name returned from SQL

❹ The SQL Server build version

❺ The user account which is connecting to SQL

❻ Was there a Successful SQL connection

❼ The SQL authentication type used

❽ The SQL authentication scheme used

❾ The SQL TCP port of the instance

❿ The IP address of the SQL Server instance

⓫ The Netbios name of the SQL Server instance

⓬ The result of ICMP echo request to the SQL Server instance

⓭ The PowerShell Remoting status of the SQL host operating system

⓮ The domain name or workgroup the SQL Server instance is joined to

⓯ The operating system version number of the machine running the dbatools command

⓰ The PowerShell version used to run the dbatools command

⓱ The version of the common language runtime

⓲ The version of the SQL Server Management Objects used

⓳ Is the local user account running the dbatools command a domain user

⓴ Is the process running the dbatools command running in an elevated session

❷❶ The PowerShell edition of the process running the dbatools command

If you don’t have a successful connection then you can see something similar to the error message in Listing 4.

Listing 4. SQL Server connection error, example failure

PS C:\> Get-DbaDatabase -SqlInstance SQLDEV01
 WARNING: [00:02:07][Get-DbaDatabase] Error occurred while establishing connection to SQLDEV01 | A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This is exactly the same result as you get if you try to connect in SSMS and are unable to get a successful connection, as seen in Figure 3.

Now that you know how to test the connection to your local default instance, you might think that all you need to do is install dbatools on every machine with a SQL Server instance and then use $Env:ComputerName. Although that works, one of the advantages of using PowerShell and dbatools is the ability to run commands against multiple instances with a single command. Returning to the SSMS analogy, you can connect to multiple remote SQL Server instances from a single laptop. How do you do the same with dbatools?

Let’s explore the SqlInstance parameter.

The SqlInstance parameter

You learned your first common default dbatools parameter SqlInstance. A PowerShell parameter follows the command name and it’s defined by the fact that it starts with a hyphen. The parameter enables the user running the command to provide input or to select options.

Every dbatools command that needs to connect to a SQL Instance has a parameter of SqlInstance (yes, there are some that don’t) to associate this to something that you’re familiar with, it’s, as you may expect, the same information that you enter into SSMS or put into a connection string. In this section we show how to use this parameter with multiple instances and non-default instances.

Single Instances

Let’s begin with connecting to a single instance.

Check a connection to a single remote default instance

When you connect to a remote default instance in SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) the name of the remote host is required. When connecting to a remote default instance with dbatools, you only need to add the name of the remote host following the -SqlInstance.

Connect-DbaInstance -SqlInstance PRODSQL01

Check a connection to a single remote named instance

If you want to test the connection to a named instance then you can use the format HOSTNAME\INSTANCENAME in exactly the same way as you use for SSMS or ADS.

PS C:\> Connect-DbaInstance -SqlInstance PRODSQL01\SHAREPOINT

Check a connection to a single local default instance

DBA is are used to using the . or localhost to represent the local hostname. When you run Test-DbaConnection against the local default instance you can use the following values for the -SqlInstance parameter:

$Env:ComputerName as seen in the previous example
the name of the machine (DEVSQL in the prior example),
localhost

Check a connection to a single local named instance

And for a local named instance you can use

$Env:ComputerName\INSTANCENAME
MACHINENAME\INSTANCENAME
localhost\INSTANCENAME

You can even force the protocol you’d like to use by using it in the connection string, as you do in SSMS. For example, to force TCP to be used to connect to SQLPROD01, you do

Multiple instances

We covered local and remote instances and named instances, but only for a single instance. The SqlInstance parameter isn’t limited to a single instance. You can run dbatools commands against multiple instances in a number of ways.

Nearly every dbatools command works against multiple instances

Which one you choose to use in the PowerShell that you write sometimes depends purely on personal preference, and other times it’s dictated by the task you’re about to perform.

Multiple instances passed as an array

If you need to run a single command against a number of instances, perhaps because someone has walked to your desk and asked “Are the 3 PRODSQL Instances working ok?”, you can list the instance names separated by a comma.

PS C:\> Connect-DbaInstance -SqlInstance PRODSQL01, PRODSQL02, PRODSQL03\ShoeFactory

Piping in instance names

Your preference might be to pipe the Instances to the command.

PS C:\> "PRODSQL01", "PRODSQL02", "PRODSQL03\ShoeFactory" | Connect-DbaInstance

Instances stored in a variable

To save repeating yourself, if you run a number of dbatools commands against the same instances, you can define a variable as a list of instances and then provide that variable to the dbatools command

PS C:\> $instances = "PRODSQL01", "PRODSQL02", "PRODSQL03\ShoeFactory"
 PS C:\> Connect-DbaInstance -SqlInstance $instances

Alternatively, your preference may be to pipe the variable to the command.

PS C:\> $instances = "PRODSQL01", "PRODSQL02", "PRODSQL03\ShoeFactory"
 PS C:\> $instances | Connect-DbaInstance

Instances from a separate source

If you have a list of instances in a database you might want to use that to be able to gather the instances for a dbatools command. If you frequently get asked by a Project Manager “Are all of my instances running?” The instances are stored in a database with a reference to the Project Manager and you know the query to gather the instance names.

You could copy and paste those instance names one by one into a SSMS connection window or add them to a folder in Central Management Server and run a query. To achieve the same result with dbatools you can use the code in Listing 5.

Listing 5. Get a list of instances from a database and set to a variable and pipe to the SqlInstance parameter

# Get Instance Names from database
 PS C:\> $instances = (Invoke-DbaQuery -SqlInstance ConfigInstance -Database DbaConfig -Query "SELECT InstanceName FROM Config.Instances C JOIN Project.People P ON C.InstanceID = P.InstanceID WHERE P.Name = 'Shawn Melton'").InstanceName
 PS C:\> $instances | Connect-DbaInstance

You may notice that the instances variable is the result of some code being wrapped in parentheses. Placing a command in parentheses () and referencing a property which is returned removes the column heading from the output. This is used in Listing 5 to ease readability.

Another way to do this is to remove the parentheses and instead pipe the results and parse with the ExpandProperty parameter in Select-Object.

PS C:\> $instances = Invoke-DbaQuery -SqlInstance ConfigInstance -Database DbaConfig -Query "SELECT InstanceName FROM Config.Instances C JOIN Project.People P ON C.InstanceID = P.InstanceID WHERE P.Name = 'Shawn Melton'" | Select-Object -ExpandProperty InstanceName
 PS C:\> $instances | Connect-DbaInstance

Each approach is valid, but we recommend that you choose one way within your coding style and stick with it. This helps keep consistency throughout your project.

Instances using a non-default port number

If you connect to your SQL Server instance using a port number then this is provided to the SqlInstance parameter in the same way that you provide it to the SSMS Connection box.

PS C:\> Connect-DbaInstance -SqlInstance "sqldev04,57689"

This is useful when the SQL Browser service isn’t enabled and your instance is on a non-default port.

Pay particular attention here to the use of quotes, as they tell PowerShell that the comma is part of the SQL instance name, and not an array.

If you use Linux or macOS and are used to the host:port syntax, we support that syntax as well.

PS C:\> Connect-DbaInstance -SqlInstance sqldev04:57689

Behind the scenes, we translate sqldev04:57689 to Microsoft’s required syntax, sqldev04,57689.

The SqlCredential parameter

Before discussing the SqlCredential parameter in-depth, we’d like to highlight the difference between SqlCredential and Credential. Back in the early days of dbatools, we agreed as a team that SqlCredential is used to connect to a SQL Server instance and Credential is used to connect to the operating system.

A little over twenty commands in dbatools use both SqlCredential and Credential. This is because they connect to both the SQL Database Engine and an OS component such as the Windows registry or a shared drive.

One such command is Test-DbaMaxMemory, which uses SqlCredential to get the maximum memory setting and Credential to calculate how many instances exist in total on the host server.

Connecting to Instances with SQL Authentication

As previously mentioned, the SqlCredential parameter is used to connect to the database engine using alternative credentials, including SQL authentication or even multi-factor authentication (MFA).

In the next example, we show how to connect to a SQL instance in dbatools using SQL Authentication. This is similar to providing a username and password in the SSMS connection box and choosing SQL Authentication, as seen in Figure 4.

Figure 4. SQL Authentication box in SSMS

You can do this with dbatools as well. You need to use the SqlCredential parameter. dbatools commands that connect to the SQL Database engines always have a SqlCredential parameter.

This is helpful when some of the instances in your estate aren’t joined to a domain or they aren’t joined to a domain which has trust with your primary domain.

It’s also useful testing the connection for applications that only support SQL authentication. In this case, you can test by providing the username for the SqlCredential parameter of the Connect-DbaInstance command.

PS C:\> Connect-DbaInstance -SqlInstance CORPSQL01 -SqlCredential devadmin

If you use PowerShell 6+ or VS Code, you will be prompted for the password as shown in Listing 6.

Listing 6. Use an alternative credential

PS C:\> Connect-DbaInstance -SqlInstance CORPSQL01 -SqlCredential devadmin
  
 PowerShell credential request
 Enter your credentials.
 Password for user devadmin:

Otherwise, it looks similar to the classic credential prompt as seen in Figure 5.

Saving the credential to use SQL Authentication with multiple commands

More often, you run more than one command against your SQL Server instances. You don’t want to be entering the password for every command.

In the same way as you saved the instances as a variable earlier, you can save your credential in memory as a variable. You do this by passing a PSCredential object to the SqlCredential parameter. The most common way of doing this is to use the Get-Credential command.

Listing 7. Assigning Credential to a variable

# Get the credential and set it to a variable
 PS C:\> $cred = Get-Credential
 # Connect to the local machine using the credential
 PS C:\> Connect-DbaInstance -SqlInstance $Env:ComputerName -SqlCredential $cred

This should result in output similar to Listing 8.

Listing 8. Using Connect-DbaInstance and Get-Credential in PowerShell 6+ and VS Code

PS C:\> $cred = Get-Credential
  
 PowerShell credential request
 Enter your credentials.
 User: devadmin
 Password for user devadmin: **********
  
 PS C:\> Connect-DbaInstance -SqlInstance $Env:ComputerName -SqlCredential $cred
  
 Name    Product              Version   Platform IsAzure IsClustered ConnectedAs (1)
 ----    -------              -------   -------- ------- ----------- -----------
 SQLDEV  Microsoft SQL Server 14.0.2027 NT x64   False   False       devadmin

You can see in the results that ConnectingAs (1) is shown as the devadmin user that we provided to the SqlCredential parameter.

Other methods of using credentials for SQL Authentication

Depending on how you store your credentials you may be able to access them programmatically. You can store credentials locally and securely using built-in PowerShell commands like Export-CliXml or using community modules like Joel Bennett’s BetterCredentials. For more information on stored credentials, visit dbatools.io/credentials.

If you choose to use stored credentials, then you can use those credentials in dbatools commands as long as you can convert them into a [PSCredential] object. The way to achieve this point depends on the product you use to secure your credentials. One method we’ve seen in the wild enables you to return a credential from a database using a stored procedure.

The example in Listing 9 shows how that could be used with the SqlCredential parameter.

Listing 9. Convert password to credential

PS C:\> $query = "EXEC GetPasswordFromPasswordStore @UserName='AD\dbatools'"
 PS C:\> $securepassword = ConvertTo-SecureString (Invoke-DbaQuery -SqlInstance VerySecure -Database NoPasswordsHere -Query $query) -AsPlainText -Force
 PS C:\> $cred = New-Object System.Management.Automation.PSCredential ("AD\dbatools", $securepassword)
 PS C:\> Test-DbaConnection -SqlInstance $Env:ComputerName -SqlCredential $cred

Note that the -Force parameter is required by ConvertTo-SecureString when converting plain-text to a SecureString. This is because passwords being transmitted as plain-text is frowned upon and should be avoided if at all possible.

Connecting to Instances with a different Windows Account

dbatools also allows you to connect to using an alternative Windows account. To do this, you can use SqlCredential to specify the alternative account’s credentials.

Listing 10. Connect using an alternative Windows or Active Directory account

PS C:\> Connect-DbaInstance -SqlInstance SQLDEV01 -SqlCredential ad\sander.stad

This even works with Azure Active Directory (AAD) and Azure SQL Database.

Listing 11. Connect using AAD

# Create a server connection
 PS C:\> $server = Connect-DbaInstance -SqlInstance dbatools.database.windows.net -SqlCredential dbatools@mycorp.onmicrosoft.com -Database inventory
 # Use server connection to query the database using our query command, Invoke-DbaQuery
 PS C:\> Invoke-DbaQuery -SqlInstance $server -Database inventory -Query "select name from instances"

And in dbatools 1.0, we even added support for multi-factor authentication (MFA)!

Listing 12. Connect using MFA

# username is the application id, password is client secret
 PS C:\> Connect-DbaInstance -SqlInstance dbatools.database.windows.net -SqlCredential 52c1fbca-24ed-4353-bbf1-6dd52f535027 -Tenant ec46e088-2707-4b0a-ab0d-dee0b52fc5c8 -Database inventory
  
 Name                                Product Version   Platform IsAzure IsClustered ConnectedAs
 ----                                ------- -------   -------- ------- ----------- -----------
 tcp:dbatools.database.windows.net           12.0.1600          True                52c1fbca-etc@ec46e088-etc

Listing 13. Perform a query using MFA

# Username is the application id, password is client secret
 PS C:\> $appcred = Get-Credential 52c1fbca-24ed-4353-bbf1-6dd52f535027
  
 # Establish a connection
 PS C:\> $server = Connect-DbaInstance -SqlInstance dbatools.database.windows.net -Database inventory -SqlCredential $appcred -Tenant 6b73c0ef-114d-43ad-94c9-85a4a82cde8b
  
 # Now that the connection is established, use it to perform a query
 PS C:\> Invoke-DbaQuery -SqlInstance $server -Database dbatools -Query "SELECT Name FROM sys.objects"
  
 Name
 ----
 sysrscols
 sysrowsets
 sysclones
 sysallocunits
 sysfiles1
 sysseobjvalues
 syspriorities
 sysdbfrag
 sysfgfrag
 ...

Alternatively, you can run the entire PowerShell process as another user. It’s good practice to log into your workstation with a user account with minimal privileges and programs with an account with elevated privileges (your alternative admin account).

To run PowerShell as a different user, right click on the PowerShell icon in the task bar, hold shift and right-click the PowerShell icon and choose Run as different user.

Figure 6. Running PowerShell as a different user

For more information on alternative credentials, including an in-depth discussion of Azure MFA, please visit dbatools.io/credentials.

The ComputerName parameter

dbatools commands use the ComputerName parameter by default. This is used in all dbatools commands which connect to a server. To associate this to something that you’re familiar with, it’s, as you may expect, the same information that you enter into a Remote Desktop Connection. You can enter hostnames, fully qualified names and IP Addresses

You can pass one or multiple servers to the ComputerName parameter in the same way as you can with the SqlInstance parameter. This means that you can list the SQL Services on multiple servers

Connect-DbaInstance enables you to check the Database Engine. When another admin comes and asks you what SQL Server features are installed on a host you can use Get-DbaService. This becomes commonplace when exploring PowerShell commands use Get-Help to understand the function of the command and the syntax.

PS C:\> Get-Help Get-DbaService
  
 Synopsis
 Gets the SQL Server related services on a computer.
  
 Description
 Gets the SQL Server related services on one or more computers.
  
 Requires Local Admin rights on destination computer(s).
  
 Syntax
 Get-DbaService [[-ComputerName] <DbaInstanceParameter[]>] [-InstanceName <String[]>] [-Credential <PSCredential>] [-Type <String[]>] [-AdvancedProperties] [-EnableException] [<CommonParameters>]
 Get-DbaService [[-ComputerName] <DbaInstanceParameter[]>] [-Credential <PSCredential>] [-ServiceName <String[]>] [-AdvancedProperties] [-EnableException] [<CommonParameters>]

Note that the description provides additional information about required privileges.

LocalAdmin permissions required

The account running the Get-DbaService command or provided to the Credential parameter must have Local Admin permissions on the remote computer

To find SQL-related services on a remote server, use the ComputerName parameter.

Listing 14. Listing the SQL Services on a remote server

PS C:\> Get-DbaService -ComputerName CORPSQL
  
 ComputerName : CORPSQL
 ServiceName  : MsDtsServer140
 ServiceType  : SSIS
 InstanceName :
 DisplayName  : SQL Server Integration Services 14.0
 StartName    : NT Service\MsDtsServer140
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL
 ServiceName  : MSSQLSERVER
 ServiceType  : Engine
 InstanceName : MSSQLSERVER
 DisplayName  : SQL Server (MSSQLSERVER)
 StartName    : NT Service\MSSQLSERVER
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL
 ServiceName  : SQLBrowser
 ServiceType  : Browser
 InstanceName :
 DisplayName  : SQL Server Browser
 StartName    : NT AUTHORITY\LOCALSERVICE
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL                        ❶
 ServiceName  : SQLSERVERAGENT                 ❷
 ServiceType  : Agent                          ❸
 InstanceName : MSSQLSERVER                    ❹
 DisplayName  : SQL Server Agent (MSSQLSERVER) ❺
 StartName    : NT Service\SQLSERVERAGENT      ❻
 State        : Stopped                        ❼
 StartMode    : Manual                         ❽

Results for both local and remote computers include:

❶ The computer name
❷ The name of the service
❸ The type of service: Agent,Browser, Engine, FullText, SSAS, SSIS, SSRS, or PolyBase
❹ The name of the SQL Server instance (if applicable)
❺ The display name of the service
❻ The service account
❼ The state of the service
❽ The start mode of the service

Note that when running this command locally there is no requirement to use the ComputerName parameter, but it’s required if it’s not local.

Methods of listing the SQL services on multiple servers

Your DBA manager asks you to identify all of the SQL Server features on a number of hosts in your test cluster. You can pass hostnames to the -ComputerName parameter using the same methods that you learned for the -SqlInstance parameter.

# Computer Names as an array
 PS C:\> Get-DbaService -ComputerName SQL01, SQL02
  
 # Computer Names piped to a command
 PS C:\> "SQL01", "SQL02" | Get-DbaService
  
 # Computer Names stored in a variable
 PS C:\> $servers = "SQL01", "SQL02"
 PS C:\> Get-DbaService -ComputerName $servers
  
 # Computer Names stored in a variable and piped to a command
 PS C:\> $servers = "SQL01", "SQL02"
 PS C:\> $servers | Get-DbaService

The Credential parameter

You may want to pass alternative credentials for connecting to the server as a different user to the one that runs the PowerShell process. dbatools commands that have a ComputerName parameter always have a Credential parameter to enable this.

List Services on a server using a different account at the command line

In the same way as you learned with SqlCredential you can provide the username with the Credential parameter and you will be prompted for the password

Listing 15. Listing Services on a server using a different user

PS C:\> Get-DbaService -ComputerName CORPSQL -Credential AD\wdurkin
  
 PowerShell credential request
 Enter your credentials.
 Password for user AD\wdurkin:

List Services on a server using a different account with a credential variable

When you’re using multiple commands, you don’t want to keep typing the password. You can also pass a PSCredential object to the Credential parameter. One way of doing this is to use the Get-Credential command.

Listing 16. Listing Services on a server using a different user

PS C:\> $cred = Get-Credential
  
 PowerShell credential request
 Enter your credentials.
 User: AD\wdurkin
 Password for user AD\wdurkin: **********
  
 PS C:\> Get-DbaService -ComputerName CORPSQL -Credential $cred

The in-console password prompt is a feature of PowerShell 6+. In earlier versions of PowerShell, expect the classic credential prompt as seen in Figure 5.

Listing SQL Services by type

Further tasks can be accomplished using Get-DbaService. To list all of the SQL Services of a certain type you can use the Type parameter. This can help you to answer questions such as:

Are all of the instances on that server using the same service account (StartName) for the database engine?

Let’s find out.

Listing 17. Listing the database engine services on a remote server

PS C:\> Get-DbaService -ComputerName CORPSQL -Type Engine
  
 ComputerName : CORPSQL
 ServiceName  : MSSQL$BOLTON
 ServiceType  : Engine
 InstanceName : BOLTON
 DisplayName  : SQL Server (BOLTON)
 StartName    : NT Service\MSSQL$BOLTON
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL
 ServiceName  : MSSQL$LONDON
 ServiceType  : Engine
 InstanceName : LONDON
 DisplayName  : SQL Server (LONDON)
 StartName    : NT Service\MSSQL$LONDON
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL
 ServiceName  : MSSQL$SQL2016
 ServiceType  : Engine
 InstanceName : SQL2016
 DisplayName  : SQL Server (SQL2016)
 StartName    : NT Service\MSSQL$SQL2016
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL
 ServiceName  : MSSQLSERVER
 ServiceType  : Engine
 InstanceName : MSSQLSERVER
 DisplayName  : SQL Server (MSSQLSERVER)
 StartName    : NT Service\MSSQLSERVER
 State        : Stopped
 StartMode    : Manual

You can also use Get-DbaService to get the services for a single instance if you have multi-instance SQL Servers. You do this using the InstanceName parameter.

Listing 18. Listing the services for a specific instance

PS C:\> Get-DbaService -ComputerName CORPSQL -InstanceName BOLTON
  
 ComputerName : CORPSQL
 ServiceName  : MSSQL$BOLTON
 ServiceType  : Engine
 InstanceName : BOLTON
 DisplayName  : SQL Server (BOLTON)
 StartName    : NT Service\MSSQL$BOLTON
 State        : Stopped
 StartMode    : Manual
  
 ComputerName : CORPSQL
 ServiceName  : SQLAgent$BOLTON
 ServiceType  : Agent
 InstanceName : BOLTON
 DisplayName  : SQL Server Agent (BOLTON)
 StartName    : NT Service\SQLAgent$BOLTON
 State        : Stopped
 StartMode    : Manual

Bonus parameter: EnableException

All of our commands except for one include the parameter EnableException. This is because by default, “sea of red” PowerShell exceptions are disabled in favor of useful and more attractive error messages.

If you’re wondering about the single command that doesn’t support EnableException, it’s Connect-DbaInstance, which supports DisableException instead.

Exception handling is bit of an advanced topic and it won’t be covered in this gentle introduction, but in the event that you’re an advanced programmer, we want to make you aware of the way we handle exceptions. For more information about this topic, please visit dbatools.io/exceptions.

Now that you’ve learned how to run a few dbatools commands and you’ve learned about four of our common parameters, you’re ready to check out the rest of the goodies in the book here.