Introduction To PowerShell For The DBA Part 2
In Part 1 of this series we began to look at how you can leverage PowerShell as a DBA and there was a brief discussion about automation. Today you will experiment with the DBATools command Copy-DbaLogin and learn about Out-Gridview and the -whatif switch.
Let’s suppose you have one, or more logins you need to get from one SQL instance to another. Perhaps you are setting up a new test environment for SQL Server 2019 and you need to move this login to that environment. Maybe this is one node of a SQL Server AlawaysOn Availability Group and you need to get this login, with its SID, on each node of the AG. Below you see I have a screenshot of a login called mysqltestlogin. It’s on a SQL Server 2017 instance.
How would we copy this with PowerShell? First, let’s use the Get-Help command to see what commands are available related to logins.
Get-Command *login* -module DBATools, sqlserver
From that output you see a command called Copy-DBAlogin in the DBATools module.
So now run the first command to see the syntax and run the second one to see all the help data, including some examples of how the command works:
Help Copy-DBALogin Help Copy-DBALogin -Full
The most common way you’re going to use this command is with a Source and Destination SQL Server name and one or more login names. See the example below.
These logins can be SQL logins or Active Directory Logins. One thing to note about this command is that the SID is copied with the login as well as the associated permissions. If you need to, you can use the -Force switch to re-copy an existing login. You might need to do this to ensure that logins, SIDs and permissions are the same between all nodes involved in a group of servers. Those SQL Servers could stand alone instances that are all servicing a particular application or they could be nodes in a Failover Cluster Instance or an Availability Group.
Copy-DBALogin -Source MySourceSQLServerIntanceName -Destination MyDestinationSQLServerInstanceName -Login Login1, Login2, Login3
Maybe you want to copy all the logins except a certain small list. In that case, use the -ExcludeLogin parameter and pass in the logins to omit from the copy operation.
For my scenario from above, my command looks like this.
Copy-DbaLogin -Source MyPCName\Kronos2017 -Destination MyPCName\Romulus2014 -Login mytestsqllogin
And the output is this.
What Is Out-GridView?
You can also use a cmdlet called Out-GridView to pass the objects of the pipeline into a GUI where you can then select the items you want to have PowerShell do the action against.
Get-DBALogin -Sqlinstance MySourceSQLInstance | Out-GridView -PassThru | Copy-DBALogin -Destination MyDestinationSQLInstance
Here are the results of that Out-GridView. Click to enlarge.
To use this for selecting only the rows of output that you want to do something with, left click the row you want to actually copy and then click the OK button in the bottom right. That will copy the selected row. You can use Shift + Click or Control + Click to do a multi-select. There is a Filter option at the top of the Out-GridView screen that also allows you to trim down the results you want to copy.
Of course, using Out-GridView re-introduces the human element to the process and so isn’t great when the goal is to completely automate a process. However, it is great if you want to see the output before you do something you don’t want to do. As a consequence, if you see results in Out-GridView that don’t match what you expect, then you can simply click on Cancel in the lower right hand corner and the PowerShell will do nothing.
Using the -WhatIf Switch In PowerShell
This brings me to another switch that helps in this scenario where you want to see what a command is going to do before you actually executing it. There is a switch called -WhatIf that will stream output showing you what a command would do if it were actually executed. This is a great tool for testing your commands. Often there will be errors or other unexpected results from a command. Using -WhatIf is a great way to catch this before you actually execute the command for real.
So far in this article, you have reviewed what you can do for a very specific object, logins. Let me suggest that you also look at what can be done at the wider scope of the SQL Server level by running the below. You may remember from the previous post that GCM is an alias for the Get-Command cmdlet.
GCM *instance* -Module DBATools, SQLServer
Here is my output for the above. Click to enlarge.
As you see, there are a lot of things you can do with commands related to the SQL Server Instance.
If you want to consider automating the scripting out of your SQL Server instance objects without buying software to do it, then check out the Help for Export-DBAInstance, which is one of the commands that is in the above list.
Help Export-DbaInstance -Full
Next Steps To Take
- Try out Copy-DBALogin in a test environment. Use it with the -WhatIf switch, or pipe it to a file by using the Out-File command to see what the command does when it runs.
- Experiment with various commands and Out-GridView in your test SQL Server environment.
- Don’t have a test SQL Server environment? Consider learning what Export-DBAInstance does and using it as part of an “Easy button” for creating a test environment on a fresh install of SQL Server. Or, look up Start-DBAMigration for this task as well.
- If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.