SQL Server 2012 with PowerShell V3 Cookbook

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

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:

Log in to SQL Server 2012 Management Studio.

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

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.

There are a few ways to get SMO installed:

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

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:

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

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

In this recipe, we will load the SQLPS module.

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):

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

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=9.0.242.0, 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.

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

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.

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

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

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:

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:

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:

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

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:

http://msdn.microsoft.com/en-us/library/cc281847.aspx

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:

Set-ExecutionPolicy RemoteSigned

PS C:\> .\myscript.ps1

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

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

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.

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>.

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

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
$server.ConnectionContext.set_LoginSecure($false)

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.

#prompt
$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:

$server.ConnectionContext.set_Login($login)
$server.ConnectionContext.set_SecurePassword($credentials.Password)

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

$login="belle"

#prompt
$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"

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>

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

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.