PowerShell in SQL Server 2012

PowerShell is the latest scripting language designed for windows administrators, system administrators and database administrators. SQL Server 2012 allows administrators to perform management and daily tasks via PowerShell. Administrator can also script SQL Server 2012 tasks with PowerShell. Admins can easily build complicated scripts with powershell since it allows for more roust logic compare to transactSQL.

 SQL Server 2012 PowerShell module loads a list of cmdlets that allows administrator of SQL Server 2012. This addin “sqlps” enable your powershell console to run specific SQL Server cmdlets, run powershell scripts and manage SQL Server objects. To load the SQL Server 2012 PowerShell add-in / module:

1. Open PowerShell console.
2. Run “Set-ExecutionPolicy unrestricted”
3. Import-module sqlps

If you wish to use SQL Objects and SMO assemblies, you need to load them manually. Use below PowerShell script to load them.

$sql2011registry="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

$item = Get-ItemProperty $sql2011registry

$powershelldir = [System.IO.Path]::GetDirectoryName($item.Path)

$smoarray =

"Microsoft.SqlServer.Management.Common",

"Microsoft.SqlServer.Dmf ",

"Microsoft.SqlServer.Smo",

"Microsoft.SqlServer.Instapi ",

"Microsoft.SqlServer.SqlWmiManagement ",

"Microsoft.SqlServer.SmoExtended ",

"Microsoft.SqlServer.ConnectionInfo ",

"Microsoft.SqlServer.SqlTDiagM ",

"Microsoft.SqlServer.SString ",

"Microsoft.SqlServer.Management.Sdk.Sfc ",

"Microsoft.SqlServer.SqlEnum ",

"Microsoft.SqlServer.RegSvrEnum ",

"Microsoft.SqlServer.WmiEnum ",

"Microsoft.SqlServer.Management.DacEnum",

"Microsoft.SqlServer.ServiceBrokerEnum ",

"Microsoft.SqlServer.Management.Collector ",

"Microsoft.SqlServer.Management.CollectorEnum",

"Microsoft.SqlServer.ConnectionInfoExtended ",

"Microsoft.SqlServer.Management.RegisteredServers ",

"Microsoft.SqlServer.Management.Dac",

"Microsoft.SqlServer.Management.Utility"

foreach ($assembly in $smoarray)

{

$assembly = [Reflection.Assembly]::LoadWithPartialName($assembly)

}

Push-Location

cd $powershelldir

update-FormatData -prependpath SQLProvider.Format.ps1xml

Pop-Location

Now that all the prep work are done, you can use “Invoke-SQLcmd –query “select * from table”” to run your SQL queries. For full list of commands, run get-command.

let’s start talking about interactions between management studio and powershell. Note that you can run Powershell directly from SQL Server 2012 management studio. This is done by simply rightclicking the database of your choice and choose “Start PowerShell”. This will set the selected database as the default path. When you run powershell directly from management studio, “sqlps” module is loaded automatically and no need to perform prep work described earlier. Prep work mentioned about is only need when running SQL commands from plain old powershell and running SQL 2011 PowerShell Scripts.

SQL Server 2012 Agent and PowerShell

You may schedule sql agent tasks using powershell scripts by including the ps1 script in your jobs. Basically you are launching powershell, loading the sqlps module and running the sql cmdlets. To do this:

Open SQL Server Management Studio

1. Open “SQL Server Agent” Node.
2. In SQL Server 2012 agent, create a new job and open the properties, go to “steps”, then choose “New”
3. Enter the name of the job, choose “PowerShell” as the type list.
4. In the Command pane, paste the powershell script content.
5. Follow on screen instructions.

Working with SQL Management Object Assemblies

SQL server Powershell allows browsing of SQL server objects as how you would browse the file system, objects can be located by path and perform actions by using its methods. Below are list of paths:

SQLSERVER:\SQL - Database objects, such as tables, views, and stored procedures.

SQLSERVER:\SQLPolicy - Policy-based management objects, such as policies and facets.

SQLSERVER:\SQLRegistration - Registered server objects, such as server groups and registered servers.

SQLSERVER:\Utility - Utility objects, such as managed instances of the Database Engine.

SQLSERVER:\DAC - Data-tier application objects such as DAC packages, and operations such as deploying a DAC

SQLSERVER:\DataCollection - Data collector objects, such as collection sets and configuration stores.