SQL Server 2012 with PowerShell V3 Cookbook

5 (1 reviews total)
By Donabel Santos
    What do you get with a Packt Subscription?

  • Instant access to this title and 7,500+ eBooks & Videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Free Chapter
    Getting Started with SQL Server and PowerShell
About 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.

Publication date:
October 2012


Chapter 1. Getting Started with SQL Server and PowerShell

In this chapter, we will cover:

  • Working with the sample code

  • Exploring the SQL Server PowerShell hierarchy

  • Installing SMO

  • Loading SMO assemblies

  • Discovering SQL-related cmdlets and modules

  • Creating a SQL Server instance object

  • Exploring SMO Server objects



PowerShell is an administrative tool that has both shell and scripting capabilities that can leverage Windows Management Instrumentation (WMI), COM components, and .NET libraries. PowerShell is becoming more prominent with each generation of Microsoft products. Support for it is being bundled, and improved, in a number of new and upcoming Microsoft product releases. Windows Server, Exchange, ActiveDirectory, SharePoint, and even SQL Server, have all shipped with added PowerShell support and cmdlets. Even vendors such as VMWare, Citrix, Cisco, and Quest, to name a few, have provided ways to allow their products to be accessible via PowerShell.

What makes PowerShell tick? Every systems administrator probably knows the pain of trying to integrate heterogeneous systems using some kind of scripting. Historically, the solution involved some kind of VBScript, some good old batch files, maybe some C# code, some Perl—you name it. Sysadmins either had to resort to duct taping different languages together to get things to work the way they intended, or just did not bother because of the complicated code.

This is where PowerShell comes in. One of the strongest points for PowerShell is that it simplifies automation and integration between different Microsoft ecosystems. As most products have support for PowerShell, getting one system to talk to another is just a matter of discovering what cmdlets, functions, or modules need to be pulled into the script. Even if the product does not have support yet for PowerShell, it most likely has .NET or COM support, which PowerShell can easily use.

Notable PowerShell V3 features

Some of the notable features in the latest PowerShell version are:

  • Workflows: PowerShell V3 introduces Windows PowerShell Workflow (PSWF), which as stated in MSDN (http://msdn.microsoft.com/en-us/library/jj134242.aspx):

    helps automate the distribution, orchestration, and completion of multi-computer tasks, freeing users and administrators to focus on higher-level tasks.

    PSWF leverages Windows Workflow Foundation 4.0 for the declarative framework, but using familiar PowerShell syntax and constructs.

  • Robust sessions: PowerShell V3 supports more robust sessions. Sessions can now be retained amid network interruptions. These sessions will remain open until they time out.

  • Scheduled jobs: There is an improved support for scheduled tasks. There are new cmdlets in the PSScheduledJob module that allow you to create, enable, and manage scheduled tasks.

  • Module AutoLoading: If you use a cmdlet that belongs to a module that hasn't been loaded yet, this will trigger PowerShell to search PSModulePath and load the first module that contains that cmdlet. This is something we can easily test:

    #check current modules in session
    #use cmdlet from CimCmdlets module, which
    #is not loaded yet
    Get-CimInstance win32_bios 
    #note new module loaded CimCmdlets
    #use cmdlet from SQLPS module, which
    #is not loaded yet
    Invoke-Sqlcmd -Query "SELECT GETDATE()" -ServerInstance "KERRIGAN"
    #note new modules loaded SQLPS and SQLASCmdlets 
  • Web service support: PowerShell V3 introduces the Invoke-WebRequest cmdlet, which sends HTTP or HTTPS requests to a web service and returns the object-based content that can easily be manipulated in PowerShell. You can think about downloading entire websites using PowerShell (and check out Lee Holmes' article on it: http://www.leeholmes.com/blog/2012/03/31/how-to-download-an-entire-wordpress-blog/).

  • Simplified language syntax: Writing your Where-Object and Foreach-Object has just become cleaner. Improvements in the language include supporting default parameter values, and simplified syntax.

    What you used to write in V1 and V2 with curly braces and $_ as follows:

    Get-Service | Where-Object { $_.Status -eq 'Running' }

    can now be rewritten in V3 as:

    Get-Service | Where-Object Status -eq 'Running'
  • Improved Integrated Scripting Environment (ISE): The new ISE comes with Intellisense, searchable commands in the sidebar, parameter forms, and live syntax checking.


Before you start: Working with SQL Server and PowerShell

Before we dive into the recipes, let's go over a few important concepts and terminologies that will help you understand how SQL Server and PowerShell can work together:

  • PSProvider and PSDrive: PowerShell allows different data stores to be accessed as if they are regular files and folders. PSProvider is similar to an adapter, which allows these data stores to be seen as drives.

    To get a list of the supported PSProvider objects, type:


    You should see something similar to the following screenshot:

    Depending on which instance of PSProvider is already available in your system, yours may be slightly different:

  • PSDrive: Think of your C:\, but for data stores other than the file system. To get a list of PSDrive objects in your system, type:


    You should see something similar to the following screenshot:

    Note that there is a PSDrive for SQLServer, which can be used to navigate, access, and manipulate SQL Server objects.

  • Execution policy: By default, PowerShell will abide by the current execution policy to determine what kind of scripts can be run. For our recipes, we are going to assume that you will run PowerShell as the administrator on your test environment. You will also need to set the execution policy to RemoteSigned :

    Set-ExecutionPolicy RemoteSigned

    This setting will allow PowerShell to run digitally-signed scripts, or local unsigned scripts.

  • Modules and snap-ins: Modules and snap-ins are ways to extend PowerShell. Both modules and snap-ins can add cmdlets and providers to your current session. Modules can additionally load functions, variables, aliases, and other tools to your session.

    Snap-ins are Dynamically Linked Libraries (DLL), and need to be registered before they can be used. Snap-ins are available in V1, V2, and V3. For example:

    Add-PSSnapin SqlServerCmdletSnapin100

    Modules, on the other hand, are more like your regular PowerShell .ps1 script files. Modules are available in V2 and V3. You do not need to register a module to use it, you just need to import:

    Import-Module SQLPS


    For more information on PowerShell basics, check out Appendix B, PowerShell Primer.


Working with the sample code

Samples in this book have been created and tested against SQL Server 2012 on Windows Server 2008 R2.


To work with the sample code in this book using a similar VM setup that the book uses, see Appendix D, Creating a SQL Server VM.

How to do it...

If you want to use your current machine without creating a separate VM, as illustrated in Appendix D, Creating a SQL Server VM, follow these steps to prepare your machine:

  1. Install SQL Server 2012 on your current operating system—either Windows 7 or Windows Server 2008 R2. See the list of upported operating systems for SQL Server 2012:


  2. Install PowerShell V3.

    • Although PowerShell V3 comes installed with Windows 8 and Windows Server 2012, at the time of writing this book these two operating systems are not listed in the list of operating systems that SQL Server 2012 supports.

    • To install PowerShell V3 on Windows 7 SP1, Windows Server 2008 SP2, or Windows Server 2008 R2 SP1:

      Install Microsoft .NET Framework 4.0, if it's not already there.

      Download and install Windows Management Framework 3.0, which contains PowerShell V3. At the time of writing this book, the Release Candidate (RC) is available from:


  3. Enable PowerShell V3 ISE. We will be using the improved Integrated Scripting Environment in many samples in this book:

    • Right-click on Windows PowerShell on your taskbar and choose Run as Administrator.

    • Execute the following:

      PS C:\Users\Administrator>Import-Module ServerManager
PS C:\Users\Administrator>Add-WindowsFeature PowerShell-ISE
    • Test to ensure you can see and launch the ISE:

      PS C:\Users\Administrator> powershell_ise

      Alternatively you can go to Start | All Programs | Accessories | Windows PowerShell | Windows PowerShell ISE.

    • Set execution policy to RemoteSigned by executing the following, on the code editor:

      Set-ExecutionPolicy RemoteSigned


      If you want to run PowerShell V2 and V3 side by side, you can check out Jeffery Hicks' article, PowerShell 2 and 3, Side by Side:


See also


Exploring the SQL Server PowerShell hierarchy

In SQL Server 2012, the original mini-shell has been deprecated, and SQLPS is now provided as a module. Launching PowerShell from SSMS now launches a Windows PowerShell session, imports the SQLPS module, and sets the current context to the item the PowerShell session was launched from. DBAs and developers can then start navigating the object hierarchy from here.

Getting ready

Log in to SQL Server 2012 Management Studio.

How to do it...

In this recipe, we will navigate the SQL Server PowerShell hierarchy by launching a PowerShell session from SQL Server Management Studio:

  1. Right-click on your instance node.

  2. Click on Start PowerShell. This will launch a PowerShell session and load the SQLPS module. This window looks similar to a command prompt, with a prompt set to the SQL Server object you launched this window from:

    Note the starting path in this window.

  3. Type dir. This should give you a list of all objects directly accessible from the current server instance—in our case, from the default SQL Server instance KERRIGAN. Note that dir is an alias for the cmdlet Get-ChildItem.

    This is similar to the objects you can find under the instance node in Object Explorer in SQL Server Management Studio.

  4. While our PowerShell window is open, let's explore the SQL Server PSDrive, or the SQL Server data store, which PowerShell treats as a series of items. Type cd\. This will change the path to the root of the current drive, which is our SQL Server PSDrive.

  5. Type dir. This will list all Items accessible from the root SQL Server PSDrive. You should see something similar to the following screenshot:

  6. Close this window.

  7. Go back to Management Studio, and right-click on one of your user databases.

  8. Click on Start PowerShell. Note that this will launch another PowerShell session, with a path that points to the database you right-clicked from:

    Note that the starting path of this window is different from the starting path when you first launched PowerShell in the second step. If you type dir from this location, you will see all items that are sitting underneath the AdventureWorks2008R2 database.

    You can see some of the items enumerated in this screen in SQL Server Management Studio's Object Explorer, if you expand the AdventureWorks2008R2 database node.

How it works...

When PowerShell is launched through Management Studio, a context-sensitive PowerShell session is created, which automatically loads the SQLPS module. This will be evident in the prompt, which by default shows the current path of the object from which the Start PowerShell menu item was clicked.

SQL Server 2008/2008 R2 was shipped with a SQLPS mini-shell, also referred to as SQLPS utility. This can also be launched from SSMS by right-clicking on an object from Object Explorer, and clicking on Start PowerShell. This mini-shell was designed to be a closed shell preloaded with SQL Server extensions. This shell was meant to be used for SQL Server only, which proved to be quite limiting because DBAs and developers often need to load additional snap-ins and modules in order to integrate SQL Server with other systems through PowerShell. The alternative way is to launch a full-fledged PowerShell session, and depending on your PowerShell version either load snap-ins or load the SQLPS module.

In SQL Server 2012, the original mini-shell has been deprecated. When you launch a PowerShell session from SSMS in SQL Server 2012, it launches the full-fledged PowerShell session, with the updated SQLPS module loaded by default.

SQL Server is exposed as a PowerShell drive (PSDrive), which allows for traversing of objects as if they are folders and files. Thus, familiar commands for traversing directories are supported in this provider, such as dir or ls. Note that these familiar commands are often just aliases to the real cmdlet name, in this case, Get-ChildItem.

When you launch PowerShell from Management Studio, you can immediately start navigating the SQL Server PowerShell hierarchy.


Installing SMO

SQL Server Management Objects (SMO) was introduced with SQL Server 2005 to allow SQL Server to be accessed and managed programmatically. SMO can be used in any .NET language, including C#, VB.NET, and PowerShell. SMO is the key to automating most SQL Server tasks. SMO is also backward compatible to previous versions of SQL Server, extending support all the way to SQL Server 2000.

SMO is comprised of two distinct classes: instance classes and utility classes.

Instance classes are the SQL Server objects. Properties of objects such as the server, the databases, and tables can be accessed and set using the instance classes.

Utility classes are helper or utility classes that accomplish common SQL Server tasks. These classes belong to one of three groups: Transfer class, Backup and Restore classes, or Scripter class.

To gain access to the SMO libraries, SMO needs to be installed, and the SQL Server-related assemblies need to be loaded.

Getting ready

There are a few ways to get SMO installed:

  • If you are installing SQL Server 2012, or already have SQL Server 2012, SMO can be installed by installing Client Tools SDK. Get your install disk or image ready.

  • If you want just SMO installed without installing SQL Server, download the SQL Server Feature 2012 pack.

How to do it...

If you are installing SQL Server or already have SQL Server:

  1. Load up your SQL Server install disk or image, and launch the setup.exe file.

  2. Select New SQL Server standalone installation or add features to an existing installation.

  3. Choose your installation type, and click on Next.

  4. In the Feature Selection window, make sure you select Client Tools SDK.

  5. Complete your installation.

After this, you should already have all the binaries needed to use SMO.

If you are not installing SQL Server, you must install SMO using the SQL Server Feature Pack on the machine you are using SMO with:

  1. Open your web browser, go to your favorite search engine, and search for SQL Server 2012 Feature Pack.

  2. Download the package.

  3. Double-click on SharedManagementObjects.msi to install.

There's more...

By default, the SMO assemblies will be installed in <SQL Server Install Directory>\110\SDK\Assemblies.


Loading SMO assemblies

Before you can use the SMO library, the assemblies need to be loaded. In SQL Server 2012, this step is easier than ever.

Getting ready

SQL Management Objects(SMO) must have already been installed on your machine.

How to do it...

In this recipe, we will load the SQLPS module.

  1. Open up your PowerShell console, or PowerShell ISE, or your favorite PowerShell editor.

  2. Type the import-module command as follows:

    Import-Module SQLPS
  3. Confirm that the module is loaded:


How it works...

The way to load SMO assemblies has changed between different versions of PowerShell. In PowerShell v1, loading assemblies can be done explicitly using the Load() or LoadWithPartialName() methods. LoadWithPartialName() accepts the partial name of the assembly, and loads from the application directory or the Global Assembly Cache (GAC):


Although LoadWithPartialName()is still supported and still remains a popular way of loading assemblies, this method should not be used because it will be deprecated in future versions.

Load() requires the fully qualified name of the assembly:

[void][Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

In PowerShell V2, assemblies can be added by using Add-Type:

Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

In PowerShell V3, loading these assemblies one by one is no longer necessary as long as the SQLPS module is loaded:

Import-Module SQLPS

There may be cases where you will still want to load specific DLL versions if you are dealing with specific SQL Server versions. Or you may want to load only specific assemblies without loading the whole SQLPS module. In this case, the Add-Type command is still the viable method of bringing the assemblies in.

There's more...

When you import the SQLPS module, you might see an error about conflicting or unapproved verbs:


The names of some imported commands from the module SQLPS include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.

This means there are some cmdlets that do not conform to the PowerShell naming convention, but the module and its containing cmdlets are still all loaded into your host. To suppress this warning, import the module with the –DisableNameChecking parameter.

See also

  • The Installing SMO recipe


Discovering SQL-related cmdlets and modules

In order to be effective at working with SQL Server and PowerShell, knowing how to explore and discover cmdlets, snap-ins, and modules is in order.

Getting ready

Log in to your SQL Server instance, and launch PowerShell ISE. If you prefer the console, you can also launch that instead.

How to do it...

In this recipe we will list the SQL-Server related commands and cmdlets.

  1. To discover SQL-related cmdlets, type the following in your PowerShell editor and run:

    #how many commands from modules that
    #have SQL in the name
    Get-Command -Module "*SQL*" | Measure-Object
    #list all the SQL-related commands
    Get-Command -Module "*SQL*" | 
    Select CommandType, Name, ModuleName | 
    Sort -Property ModuleName, CommandType, Name | 
    Format-Table -AutoSize

    After you execute the line, your output window should look similar to the following screenshot:

  2. To see which of these modules are loaded, type the following in your editor and run:

    Get-Module -Name "*SQL*"

    If you have already used any of the cmdlets in the previous step, then you should see both SQLPS and SQLASCMDLETS. Otherwise, you will need to load these modules before you can use them.

  3. To explicitly load these modules, type the following and run:

    Import-Module -Name "SQLPS"

    Note that SQLASCMDLETS will be loaded when you load SQLPS.

How it works...

At the core of PowerShell are cmdlets. A cmdlet (pronounced commandlet) can be a compiled, reusable .NET code, or an advanced function, or a workflow that typically performs a very specific task. All cmdlets follow the verb-noun naming notation.

PowerShell ships with many cmdlets and can be further extended if the shipped cmdlets are not sufficient for your purposes.

A legacy way of extending PowerShell is by registering additional snap-ins. A snap-in is a binary, or a DLL, that contains cmdlets. You can create your own by building your own .NET source, compiling, and registering the snap-in. You will always need to register snap-ins before you can use them. Snap-ins are a popular way of extending PowerShell.

The following table summarizes common tasks with snap-ins:



List loaded snap-ins


List installed snap-ins

Get-PSSnapin -Registered

Show commands in a snap-in

Get-Command -Module "SnapinName"

Load a specific snap-in

Add-PSSnapin "SnapinName"

When starting, PowerShell V2, modules are available as the improved and preferred method of extending PowerShell.

A module is a package that can contain cmdlets, providers, functions, variables, and aliases. In PowerShell V2, modules are not loaded by default, so required modules need to be explicitly imported.

Common tasks with modules are summarized in the following table:



List loaded modules


List installed modules

Get-Module -ListAvailable

Show commands in a module

Get-Command -Module "ModuleName"

Load a specific module

Import-Module -Name "ModuleName"

One of the improved features with PowerShell V3 is that it supports autoloading modules. You do not need to always explicitly load modules before using the contained cmdlets. Using the cmdlet in your script is enough to trigger PowerShell to load the module that contains it.

The SQL Server 2012 modules are located in the PowerShell/Modules folder of the Install directory:

There's more...

The following table shows the list of the SQLPS and SQLASCMDLETS cmdlets of this version:

CommandType Name


Cmdlet Add-RoleMember


Cmdlet Backup-ASDatabase


Cmdlet Invoke-ASCmd


Cmdlet Invoke-ProcessCube


Cmdlet Invoke-ProcessDimension


Cmdlet Invoke-ProcessPartition


Cmdlet Merge-Partition


Cmdlet New-RestoreFolder


Cmdlet New-RestoreLocation


Cmdlet Remove-RoleMember


Cmdlet Restore-ASDatabase


Cmdlet Add-SqlAvailabilityDatabase


Cmdlet Add-SqlAvailabilityGroupListenerStaticIp


Cmdlet Backup-SqlDatabase


Cmdlet Convert-UrnToPath


Cmdlet Decode-SqlName


Cmdlet Disable-SqlHADRService


Cmdlet Enable-SqlHADRService


Cmdlet Encode-SqlName


Cmdlet Invoke-PolicyEvaluation


Cmdlet Invoke-Sqlcmd


Cmdlet Join-SqlAvailabilityGroup


Cmdlet New-SqlAvailabilityGroup


Cmdlet New-SqlAvailabilityGroupListener


Cmdlet New-SqlAvailabilityReplica


Cmdlet New-SqlHADREndpoint


Cmdlet Remove-SqlAvailabilityDatabase


Cmdlet Remove-SqlAvailabilityGroup


Cmdlet Remove-SqlAvailabilityReplica


Cmdlet Restore-SqlDatabase


Cmdlet Resume-SqlAvailabilityDatabase


Cmdlet Set-SqlAvailabilityGroup


Cmdlet Set-SqlAvailabilityGroupListener


Cmdlet Set-SqlAvailabilityReplica


Cmdlet Set-SqlHADREndpoint


Cmdlet Suspend-SqlAvailabilityDatabase


Cmdlet Switch-SqlAvailabilityGroup


Cmdlet Test-SqlAvailabilityGroup


Cmdlet Test-SqlAvailabilityReplica




To learn more about these cmdlets, use the Get-Help cmdlet. For example:

Get-Help "Invoke-Sqlcmd" 
Get-Help "Invoke-Sqlcmd" -Detailed
Get-Help "Invoke-Sqlcmd" -Examples
Get-Help "Invoke-Sqlcmd" -Full

You can also check out the MSDN article on SQL Server database engine cmdlets:


When you load the SQLPS module, several assemblies are loaded into your host.

To get a list of SQL Server-related assemblies loaded with the SQLPS module, use the following script, which will work in both PowerShell V2 and V3:

Import-Module SQLPS –DisableNameChecking

[appdomain]::CurrentDomain.GetAssemblies() | 
Where {$_.FullName -match "SqlServer" } | 
Select FullName

If you want to run on a strictly V3 environment, you can take advantage of the simplified syntax:

Import-Module SQLPS –DisableNameChecking

[appdomain]::CurrentDomain.GetAssemblies() | 
Where FullName -match "SqlServer" | 
Select FullName

This will show you all the loaded assemblies, including their public key tokens:

More information on running PowerShell scripts

By default, PowerShell is running in restricted mode, in other words, it does not run scripts. To run our scripts from the book, we will set the execution policy to RemoteSigned as follows:

Set-ExecutionPolicy RemoteSigned


See the Execution policy section in Appendix B, PowerShell Primer, for further explanation of different execution policies.

If you save your PowerShell code in a file, you need to ensure it has a .ps1 extension otherwise PowerShell will not run it. Ideally the filename you give your script does not have spaces. You can run this script from the PowerShell console simply by calling the name. For example if you have a script called myscript.ps1 located in the C:\ directory, this is how you would invoke it:

PS C:\> .\myscript.ps1

If the file or path to the file has spaces, then you will need to enclose the full path and file name in single or double quotes, and use the call (&) operator:

PS C:\>&'.\my script.ps1'

If you want to retain the variables and functions included in the script, in memory, thus making them available globally in your session, then you will need to dot source the script. To dot source is literally to prefix the filename, or the path to the file, with a dot and a space:

PS C:\> . .\myscript.ps1
PS C:\> . '.\my script.ps1'

More information on mixed assembly error

You may encounter an error when running some commands that are built using older .NET versions. Interestingly, you may see this while running your script in the PowerShell ISE, but not necessarily in the shell.

Invoke-Sqlcmd: Mixed mode assembly is built against version 'V2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information.

A few steps are required to solve this issue:

  1. Open Windows Explorer.

  2. Identify the Windows PowerShell ISE install folder path. You can find this out by going to Start | All Programs | Accessories | Windows | PowerShell, and then right-clicking on the Windows PowerShell ISE menu item and choosing Properties.

    For the 32-bit ISE, this is the default path:


    For the 64-bit ISE, this is the default path:


  3. Go to the PowerShell ISE Install folder.

  4. Create an empty file called powershell_ise.exe.config.

  5. Add the following snippet to the content and save the file:

    <?xml version="1.0" encoding="utf-8" ?>
    <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" />
    <generatePublisherEvidence enabled="false" />
  6. Reopen PowerShell ISE and retry the command that failed.


Creating a SQL Server instance object

Most of what you will need to do in SQL Server will require a connection to an instance.

Getting ready

Open up your PowerShell console, the PowerShell ISE, or your favorite PowerShell editor.

You will need to note what your instance name is. If you have a default instance, you can use your machine name. If you have a named instance, the format will be <machine name>\<instance name>.

How to do it...

If you are connecting to your instance using Windows authentication, and using your current Windows login, follow these steps:

  1. Import the SQLPS module:

    #import SQLPS module
    Import-Module SQLPS –DisableNameChecking
  2. Store your instance name in a variable as follows:

    #create a variable for your instance name
    $instanceName = "KERRIGAN"
  3. If you are connecting to your instance using Windows authentication using the account you are logged in as:

    #create your server instance
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

    If you are connecting using SQL Authentication, you will need to know the username and password that you will use to authenticate. In this case, you will need to add the following code, which will set the connection to mixed mode and prompt for the username and password:

    #set connection to mixed mode
    #set the login name
    #of course we don't want to hardcode credentials here
    #so we will prompt the user
    #note password is passed as a SecureString type
    $credentials = Get-Credential
    #remove leading backslash in username
    $login = $credentials.UserName -replace("\\", "")
    #check connection string
    Write-Verbose "Connected to $($server.Name)"
    Write-Verbose "Logged in as $($server.ConnectionContext.TrueLogin)"

How it works...

Before you can access or manipulate SQL Server programmatically, you will often need to create references to its objects. At the most basic is the server.

The server instance uses the type Microsoft.SqlServer.Management.Smo.Server. By default, connections to the server are made using trusted connections, meaning it uses the Windows account you're currently using when you log into the server. So all it needs is the instance name in its argument list:

#create your server instance
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

If, however, you need to connect using a SQL login, you will need to set the ConnectionContext.LoginSecure property of the SMO Server class setting to false:

#set connection to mixed mode

You will also need to explicitly set the username and the password. The best way to accomplish this is to prompt the user for the credentials.

$credentials = Get-Credential

The credential window will capture the login and password. The Get-Credential cmdlet returns the username with a leading backslash if the domain is not specified. In this case, we want to remove this leading backslash.

#remove leading backslash in username
$login = $credentials.UserName -replace("\\","")

Once we have the login, we can pass it to the set_Login method. The password is already a SecureString type, which is what the set_SecurePassword expects, so we can readily pass this to the method:


Should you want to hardcode the username and just prompt for the password, you can also do this:


$credentials = Get-Credential –Credential $login

In the script, you will also notice we are using Write-Verbose instead of Write-Host to display our results. This is because we want to be able to control the output without needing to always go back to our script and remove all the Write-Host commands.

By default, the script will not display any output, that is, the $VerbosePreference special variable is set to SilentlyContinue. If you want to run the script in verbose mode, you simply need to add this line in the beginning of your script:

$VerbosePreference = "Continue"

When you are done, you just need to revert the value to SilentlyContinue:

$VerbosePreference = "SilentlyContinue"

See also

  • The Loading SMO assemblies recipe

  • The Creating SQL Server instance using SMO recipe


Exploring SMO server objects

SQL Management Objects (SMO) comes with a hierarchy of objects that are accessible programmatically. For example, when we create an SMO server variable, we can then access databases, logins, and database-level triggers. Once we get a handle of individual databases, we can then traverse the tables, stored procedures, and views that it contains. Since many tasks involve SMO objects, you will be at an advantage if you know how to discover and navigate these objects.

Getting ready

Open up your PowerShell console, the PowerShell ISE, or your favorite PowerShell editor.

You will also need to note what your instance name is. If you have a default instance, you can use your machine name. If you have a named instance, the format will be <machine name>\<instance name>

How to do it...

In this recipe, we will start exploring the hierarchy of objects with SMO.

  1. Import the SQLPS module as follows:

    Import-Module SQLPS -DisableNameChecking
  2. Create a server instance as follows:

    $instanceName = "KERRIGAN"
    $server = New-Object `
            -TypeName Microsoft.SqlServer.Management.Smo.Server `
            -ArgumentList $instanceName
  3. Get the SMO objects directly accessible from the $server object:

    $server | 
    Get-Member -MemberType "Property" | 
    Where Definition -like "*Smo*"
  4. Now let's check SMO objects under databases. Execute the following line:

    $server.Databases | 
    Get-Member -MemberType "Property" | 
    Where Definition -like "*Smo*"
  5. To check out the tables, you can type and execute:

    $server.Databases["AdventureWorks2008R2"].Tables | 
    Get-Member -MemberType "Property" | 
    Where Definition -like "*Smo*"

How it works...

SMO contains a hierarchy of objects. At the very top there is a server object, which in turn contains objects such as Databases, Configuration, SqlMail, LoginCollection, and the like. These objects in turn contain other objects, for example, Databases is a collection that contains Database objects, and a Database in turn, contains Tables and so on.

See also

About the Author
  • Donabel Santos

    Donabel Santos is a self-confessed data geek. She loves working with data, writing queries, and developing reports on her SQL Server databases, and exploring and visualizing data with Tableau.

    She is the principal and senior business intelligence architect at QueryWorks Solutions, a Tableau Learning and Alliance partner in Vancouver, BC, Canada, providing consulting and training services. She has spent years in consulting and has developed a variety of solutions for clients in different verticals—finance, manufacturing, healthcare, legal, higher education, and local government.

    Donabel is a multi-year Microsoft Data Platform MVP (previously known as SQL Server MVP) and has extensive experience in the SQL server in different areas, such as development, administration, data warehouse, reporting (SSRS), tuning, troubleshooting, XML, CLR, and integration with ERPs and CRMs using PowerShell, C#, SSIS, and Power BI.

    One of Donabel's passions is teaching and sharing her love for data. She is a Tableau Certified Professional and a Tableau accredited trainer, delivering Tableau public and on-site client training. She is also the lead instructor for a number of courses at British Columbia Institute of Technology (BCIT), including Applied Database Administration and Design (ADAD) and Applied Data Analytics (ADA) programs. She teaches SQL server administration, development, integration (SSIS), data warehouse foundations, and visual analytics with Tableau.

    Donabel has also authored three other books with Packt Publishing: SQL Server 2012 with PowerShell V3 Cookbook, PowerShell for SQL Server Essentials, and SQL Server 2014 with PowerShell V5 Cookbook. She also contributed a chapter to Manning Publications' PowerShell Deep Dives.

    Browse publications by this author
Latest Reviews (1 reviews total)
SQL Server 2012 with PowerShell V3 Cookbook
Unlock this book and the full library FREE for 7 days
Start now