Windows PowerShell is quickly becoming Microsoft’s management standard for all of its server products. However, PowerShell has never really taken off in the SQL Server community.
There’s no denying that PowerShell is more complex than Windows Shell scripting or T-SQL for that matter. And who needs yet another thing to learn? However, PowerShell offers advantages such as multi-server management and advanced .NET integration capabilities that aren’t present in Windows Shell scripting and T-SQL.
Related: PowerShell the SQL Server Way
If you’re just getting started with PowerShell, here are some of the common problems that you’re likely to run into and their answers.
Q: Why don’t SQL Server cmdlets run from the PowerShell command line? When I try to use the Invoke-Sqlcmd cmdlet I get the error “The term 'Invoke-Sqlcmd' is not recognized as a cmdlet.”
A: Before you can use the SQL Server PowerShell Provider, you need to import the sqlps module into your Windows Server PowerShell environment. The easiest way to do so is to just type sqlps at either the Windows Command Shell or the PowerShell command prompt.
Alternately, you can start the PowerShell command prompt with the sqlps module loaded by opening SQL Server Management Studio (SSMS), then right-clicking in Object Explorer and selecting the Start PowerShell option from the context menu.
Q: Why can’t I run scripts? My PowerShell script ends with .ps1 but when I try to run it, I get the error “File <filename> cannot be loaded because the execution of scripts is disabled on this system.”
A: By default PowerShell is set so that it runs only interactive commands. This is a security measure to protect systems from running unauthorized scripts.
PowerShell controls the execution of scripts by its Scripting Execution Policy. The default value of the Scripting Execution Policy is Restricted. You can run PowerShell scripts by setting the Scripting Execution Policy to Unrestricted, thus:
Set-ExecutionPolicy Unrestricted
This allows all PowerShell scripts to be executed. Alternately, for a somewhat more secure way you can choose the RemoteSigned value. RemoteSigned allows any scripts that you have written to run other scripts that have an associated digital signature from a trusted publisher. You set the RemoteSigned Execution Policy as follows:
Set-ExecutionPolicy RemoteSigned
Q: How can I use PowerShell to list databases?
A: One thing that makes PowerShell difficult to learn is that it provides multiple ways to do things. For example, you can use PowerShell to get a list of your server’s databases by using PowerShell’s path navigation. From the sqlps command prompt, enter this:
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases Get-ChildItem
Alternatively, you can use the SMO objects from PowerShell to list databases, as you can see in the following code listing:
$sqlserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “$DBServer” $sqlserver.databases | select Name
Q: How do I run a SQL Server query using PowerShell?
A: Again, there are multiple ways to execute queries on SQL Server from PowerShell. After the sqlps module has been loaded, the most straightforward method is to use the Invoke-Sqlcmd cmdlet as you can see in the following example:
Invoke-Sqlcmd -database "AdventureWorks" -Query "SELECT * FROM Production.Product Where Color = 'Black'"
Related: Why PowerShell for SQL Server?
