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:

  Get-PSProvider

  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:

  Get-PSDrive

  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

  Note

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