SQL Server Powershell Snapin and how to run SQL Scripts

Some of this is from Dan’s Blog

There are two SQL Server Snapins you need to load into your PowerShell session: SQLServerProviderSnapin100 and SQLServerCmdletSnapin100.  They ship with SQL 2008 and up. These ship with SQL Server 2008 and SQL Server 2008 R2.The Provider snapin is explained here. The Cmdlet snapin is explained here. You can also add to your PowerShell profile or keep it in each of your scripts. See Technet : Good article here

Installing SQL Server PowerShell Support from the SQL CD

(I tried to install from the feature pack but no go)
——————————————————————————–
You install the software required to run Windows PowerShell scripts by using SQL Server Setup. Beginning in SQL Server 2008, Setup installs the following Windows PowerShell components when you select either the client software or the Database Services nodes:

Windows PowerShell 1.0, if Windows PowerShell is not already present on your computer.

The SQL Server snap-ins. The snap-ins are dll files that implement two types of Windows PowerShell support for SQL Server:

A set of SQL Server cmdlets. Cmdlets are commands that implement a specific action. For example, Invoke-Sqlcmd runs a Transact-SQL or XQuery script that can also be run by using the sqlcmd utility, and Invoke-PolicyEvaluation reports whether SQL Server objects comply with policy-based management policies.

A SQL Server provider. The provider lets you navigate the hierarchy of SQL Server objects using a path similar to a file system path. Each object is associated with a class from the SQL Server Management object models. You can use the methods and properties of the class to perform work on the objects. For example, if you cd to a databases object in a path, you can use the methods and properties of the Microsoft.SqlServer.Managment.SMO.Database class to manage the database.

The sqlps utility that is used to run Windows PowerShell sessions that include the SQL Server snap-ins.

Beginning in SQL Server 2008, SQL Server Management Studio supports starting Windows PowerShell sessions from the Object Explorer tree. Also beginning in SQL Server 2008, SQL Server Agent supports Windows PowerShell job steps.

SQL Server cmdlets implement SQL Server commands. One of the cmdlets is Invoke-Sqlcmd. This is used to run Database Engine Query scripts to be run with the sqlcmd utility.

# Load SqlServerCmdletSnapin100

if (!(Get-PSSnapin | ?{$_.name -eq ‘SqlServerCmdletSnapin100’}))
{
if(Get-PSSnapin -registered | ?{$_.name -eq ‘SqlServerCmdletSnapin100’})
{
add-pssnapin SqlServerCmdletSnapin100
write-host "Loading SqlServerCmdletSnapin100 in session"
}
else
{
write-host "SqlServerCmdletSnapin100 is not registered with the system."
   break
}
}
else
{
write-host "SqlServerCmdletSnapin100 is already loaded"
}

Here is an example

$AddReportAuthors = "CREATE LOGIN [RLAN\ReportAuthors] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]"
$a = (Invoke-sqlcmd -ServerInstance sql1\sql1 -Database "master" -Query $AddReportAuthors)
$a

A SQL Server PowerShell provider exposes the SQL Server management object model hierarchies as PowerShell paths that are similar to file system paths. You can use the SQL Server management object model classes to manage the objects represented at each node of the path. SQL Server provides these features for running PowerShell:

  • The sqlps PowerShell module that can be imported to a PowerShell session, the module then loads the SQL Server snap-ins. You can interactively run ad hoc PowerShell commands. You can run script files using a command such as .\MyFolder\MyScript.ps1.

  • PowerShell script files can be used as input to SQL Server Agent PowerShell job steps that run the scripts either at scheduled intervals or in response to system events.

  • The sqlps utility that starts PowerShell and imports the SQL Server module. You can then perform all actions supported by the module. You can start the sqlps utility either in a command prompt or by right-clicking on the nodes in the SQL Server Management Studio Object Explorer tree and selecting Start PowerShell.

    # Load SqlServerProviderSnapin100
    if (!(Get-PSSnapin | ?{$_.name -eq ‘SqlServerProviderSnapin110’}))
    {
    if(Get-PSSnapin -registered | ?{$_.name -eq ‘SqlServerProviderSnapin110’})
    {
    add-pssnapin SqlServerProviderSnapin100
    write-host "Loading SqlServerProviderSnapin100 in session"
    }
    else
    {
    write-host "SqlServerProviderSnapin100 is not registered with the system." -Backgroundcolor Red –Foregroundcolor White
       break
    }
    }
    else
    {
    write-host "SqlServerProviderSnapin100 is already loaded"

Advertisements