Running SQL scripts

PowerShell has a very useful Invoke-SQLCmd cmdlet to run T-SQL scripts if you install the sqlps module. This module is installed as part of SQL Server Management Studio (SSMS) but you may not want to install the whole package just to run scripts. However you can install just the PowerShell module and a couple of dependencies.

First you need to download module and two dependencies. There are different versions for SQL 2012, 2014 and 2016 but all can manage instances of SQL Server back to 2000 (SQL Server 2000 must be running SP4 or later and SQL Server 2005 SP2 or later) so there is little reason not to just go with SQL 2016. You need the PowerShell Extensions for Microsoft SQL Server (PowerShellTools.msi) module plus the dependencies SQL Server Shared Management Objects (SharedManagementObjects.msi) and CLR Types for Microsoft SQL Server (SQLSysClrTypes.msi). All are available in both 32- and 64-bit depending upon which version of PowerShell you want to run.

SQL Server 2016 Feature Pack (download button and tick packages)
SQL Server 2014 Feature Pack (download button and tick packages)
SQL Server 2012 Feature Pack (links directly to the package download in the Install Instructions tab)

Once downloaded you need to install the CLR Types first, following by the Shared Management Objects and then the PowerShell module. This can be automated with the following commands:

msiexec /i SQLSysClrTypes.msi /qn
msiexec /i SharedManagementObjects.msi /qn
msiexec /i PowerShellTools.msi /qn

Once installed you are ready to go. Later versions of PowerShell will implicitly import the module as needed. Earlier versions will need to add the line Import-Module sqlps

To quickly test everything is working, open a new PowerShell console window and enter the following

Import-Module sqlps
Invoke-Sqlcmd -ServerInstance servername -Query "SELECT @@SERVERNAME"

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s