Applies to: 
SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
install sql server powershell
You are reading: Sql sever books online
There are two sql server powershell modules; sqlserver and sqlps.
the sqlserver module is the current powershell module to use.
The sqlps module is included with the sql server installation (for backwards compatibility), but is no longer updated.
The sqlserver module contains updated versions of the cmdlets in sqlps and includes new cmdlets to support the latest sql features.
Previous versions of the sqlserver module were included with sql server management studio (ssms), but only with 16.x versions of ssms.
To use powershell with ssms 17.0 and later, install the sqlserver module from the powershell gallery.
you can also use powershell with azure data studio.
See Also: Harry Potter: What Happened To Every Major Character After The Deathly Hallows?
why did the module change from sqlps to sqlserver?
In order to push sql powershell updates, we had to change the identity of the sql powershell module and the container known as sqlps.exe. Due to this change, there are now two sql powershell modules, the sqlserver module and the sqlps module.
update your powershell scripts if you import the sqlps module.
If you have powershell scripts that run import-module -name sqlps and you want to take advantage of the new provider functionality and new cmdlets, you should change them to import-module -name sqlserver. the new module is installed in the %programfiles%windowspowershellmodulessqlserver folder. as such, you don’t have to update the $env:psmodulepath variable. if you have scripts that use a third-party or community version of a module named sqlserver, use the prefix parameter to avoid name collisions.
It is recommended to start your script with import-module sqlserver to avoid problems in parallel if the sqlps module is installed on the same machine.
This section applies to scripts executed from powershell and not from the sql agent. the new module can be used with sql agent job steps using #nosqlps.
sql server powershell components
the sqlserver module comes with:
-
powershell providers, which allow a simple navigation mechanism similar to file system paths. you can create paths similar to file system paths, where the drive is associated with a sql server management object model and the nodes are based on the object model’s classes. then you can use familiar commands like cd (alias for set location) and dir (alias for get-childitem) to navigate paths similar to how you navigate folders . in a command prompt window. you can use other commands, such as ren (alias to rename the element) or del (alias to remove the element), to perform actions on nodes in the stream.
a set of cmdlets that support actions such as running a sqlcmd script that contains transact-sql or xquery statements.
the ace provider and cmdlets, which were previously installed separately.
sql server versions
See Also: Motivational Books: The 31 Best Motivational Books of All Time
sql powershell cmdlets can be used to manage azure sql database instances, azure synapse analytics, and all supported sql server products.
sql server identifiers containing unsupported characters in powershell paths
The encode-sqlname (aliases for convertto-encodedsqlname) and decode-sqlname (aliases for convertfrom-encodedsqlname) cmdlets help you specify sql server identifiers that contain characters not supported by powershell paths. for more information see sql server identifiers in powershell.
Use the convert-urntopath cmdlet to convert a unique resource name for a database engine object to a path for the sql server powershell provider. For more information, see convert urns to sql server provider paths.
query expressions and uniform resource names
Query expressions are strings that use xpath-like syntax to specify a set of criteria that enumerate one or more objects in an object model hierarchy. a uniform resource name (urn) is a specific type of query expression string that uniquely identifies a single object. for more information, see query expressions and uniform resource names.
sql server agent
there is no change in the module used by the sql server agent. as such, sql server agent jobs, which have powershell-like job steps, use the sqlps module. For more information, see how to run powershell with sql server agent. however, as of sql server 2019, you can disable sqlps. To do this, in the first line of a powershell job step you can add #nosqlps, which prevents the sqlps module from being automatically loaded by the sql agent. when you do this, your sql agent job runs the version of powershell installed on the machine, and then you can use any other powershell modules you want.
sqlps troubleshooting
If you see the agent job steps (powershell subsystem) fail with the following error, this section may be helpful in troubleshooting the problem.
A job step received an error on line 1 in a powershell script. the corresponding line is ‘import-module sqlps’. correct the script and reschedule the job. the error information returned by powershell is: ‘the specified module ‘sqlps’ was not loaded because no valid module file was found in any module directory.
The sqlps module must be available in the environment variable psmodulepath. uninstalling ssms 16.x may remove sqlps from psmodulepath. To check the current values stored in psmodulepath, run the following powershell:
if the path is set, you will see an entry similar to c:program files (x86)microsoft sql server130toolspowershellmodules. if the path is not set, locate the sqlps folder on your server and add it to the environment variable value via powershell or in system properties>advanced>environment variables.
sqlserver module with sql agent
If you want to use the sqlserver module in your sql agent job step, you can put this code in the first two lines of your script.
See Also: 50 Great Books for Toddlers – My Bored Toddler Teacher Approved!
cmdlet reference
- sqlserver cmdlets
- sqlps cmdlets
next steps
- download sql server powershell module
- sql server powershell cmdlets
- use powershell with azure data studio