PowerShell is a great tool that allows DBAs and Developers alike to script, manage and automate SQL Server tasks. Most of the objects and tasks will require SMO (SQL Server Management Objects).
Basic Steps
1. Set Execution Policy
Depending on what you need to do, you may need to set the Execution Policy in your Powershell console. In my case, I will be using it to access SQL Server for administrative purposes, so I will be setting mine to have Unrestricted access to my server. Make sure you review the security guidelines in your organization before you set your environment to Unrestricted
PS> Set-ExecutionPolicy Unrestricted |
2. Load assemblies
First, you will need to load SQL Server related assemblies. For SMO, you will usually want to load the following assemblies:
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SqlEnum
- Microsoft.SqlServer.SmoEnum
- Microsoft.SqlServer.ConnectionInfo
When you load the assemblies, you have to use the following format:
[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") |
A couple of things to note here:
- the backtick is the line continuation character, ie if your line of code spans multiple lines, you have to use a backtick
- you can get the version of the assembly either by :
- going to the GAC (C:Windowsassembly), right click on assembly and click on properties
- use reflector
