SQLPS module to Powershell helps to do so many things on SQL Server without SSMS and the main objective is to automate tasks for your environment.
This module isn’t installed by default, so there are a couple of steps before use it. First, install the module and the simple cmdlet install-module SQLPS won’t work.
To install the module you need install a couple of things (if you have SQL Server instance there is already installed).
The installation is quite simple and isn’t necessary to restart the system. There are 3 files necessary and need to install in the sequence below.
- System CLR Types for Microsoft® SQL Server® 2012 – SQLSysClrTypes.msi
- SQL Server® 2012 Shared Management Objects – SharedManagementObjects.msi
- PowerShell Extensions for Microsoft SQL Server 2012 – PowerShellTools.MSI
With all installed let’s try to import the module on powershell.
Actually that didn’t work (yet). In the error message shows it’s not allowed to run scripts in my system.
Next step is to change the execution policy to run scripts in the system. Let’s try it using the cmdlets Get-ExecutionPolicy to know the actual value and Set-ExecutionPolicy to change it. ( more information )
Ok, second error and this time because I’m trying to change to RemoteSigned and only as administrator I can change it.
Running as administrator the local machine execution policy has changed and the SQLPS module can be imported and use the cmdlets.