Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos
Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By: Donabel Santos

Overview of this book

PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand."SQL Server 2012 with PowerShell V3 Cookbook" provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then deep dives into more administration topics like backup and restore, credentials, policies, jobs.Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports. A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, "SQL Server 2012 with PowerShell V3 Cookbook" will be your go-to reference in automating and managing SQL Server.
Table of Contents (21 chapters)
SQL Server 2012 with PowerShell V3 Cookbook
Credits
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
Resources
Index

Converting script into functions


A function is a reusable, callable code block(s). A function can accept parameters, and can produce different results based on values that are passed to it.

A typical anatomy of a PowerShell function looks like:

function Do-Something
{
    <#
       comment based help
    #>
    param
    (
       #parameters
    )    
    #blocks of code
}

To illustrate, let's create a very simple function that takes a report server URL and lists all items in that report server. This function will take in a parameter for the report server URL, and another switch called $ReportsOnly, which can toggle displaying between all items, or only report items.

function Get-SSRSItems
{
    <#
       comment based help
    #>
    param
    (
       [Parameter(Position=0,Mandatory=$true)]
       [alias("reportServer")]
       [string]$ReportServerUri,
       [switch]$ReportsOnly
    )    

    Write-Verbose "Processing $($ReportServerUri) ..."
    $proxy = New-WebServiceProxy `
        -Uri $ReportServerUri `
        -UseDefaultCredential 
    if ($ReportsOnly)
    {
        $proxy.ListChildren("/", $true) |
        Where TypeName -eq "Report"
    }
    else
    {
        $proxy.ListChildren("/", $true) 
    }         
}

To call this function, we can pass in the value for –ReportServerUri and also set the –ReportsOnly switch:

$server = "http://server1/ReportServer/ReportService2010.asmx"

Get-SSRSItems -ReportsOnly -ReportServerUri $server |
Select Path, TypeName |
Format-Table -AutoSize 

To allow your function to behave more like a cmdlet and work with the pipeline, we will need to add the [CmdletBinding()] attribute. We can also change the parameters to enable values to come from the pipeline by using ValueFromPipeline=$true. Inside the function definition, we will need to add three blocks:

  • BEGIN

    Preprocessing; anything in this block will be executed once when the function is called.

  • PROCESS

    Actual processing that is done for each item that is passed in the pipeline.

  • END

    Post-processing; this block will be executed once before the function terminates executing.

We will also need to specify in the parameter block that we want to accept input from the pipeline.

A revised function follows:

function Get-SSRSItems
{
    <#
       comment based help
    #>
    [CmdletBinding()]
    param
    (
       [Parameter(Position=0,Mandatory=$true,
                  ValueFromPipeline=$true,
                  ValueFromPipelineByPropertyName=$true)]
       [alias("reportServer")]
       [string]$ReportServerUri,
       [switch]$ReportsOnly
    )    
    BEGIN
    {
    }
    PROCESS
    {
        Write-Verbose "Processing $($ReportServerUri) ..."
        $proxy = New-WebServiceProxy `
                 -Uri $ReportServerUri -UseDefaultCredential 
        if ($ReportsOnly)
        {
          $proxy.ListChildren("/", $true) |
          Where TypeName -eq "Report"
        }
        else
        {
         $proxy.ListChildren("/", $true) 
        }         
    }
    END
    {
        Write-Verbose "Finished processing"
    }
}

To invoke, we can pipe an array of servers to the Get-SSRSItems function, and this automatically maps the servers to our –ReportServerUri parameter since we specified ValueFromPipeline=$true. Note that Get-SSRSItems will get invoked for each value in our array:

$servers = @("http://server1/ReportServer/ReportService2010.asmx", "http://server2/ReportServer/ReportService2010.asmx")

$servers | 
Get-SSRSItems -Verbose -ReportsOnly |
Select Path, TypeName |
Format-Table -AutoSize