for the last 1.5 year, in all my “PowerShell and SQL Server” presentations, I have shown some basic samples on how to use both technologies together. Let me be clear, in SQL Server 2008(including R2), SQLPS.exe is a PS mini-shell built on PS v1 which means that PowerShell V2 additional cmdlets and features wouldn’t be available from within SQLPS. For this reason you can’t use PowerShell V2 scripts in a SQL Agent job.
Now, looking outside of the box, thanks to SQL Server 2008, you can load the SQL snapins/provider & use PS V2 to extend management on your SQL box. To load the SQL provider with PowerShell v2, follow the instructions from Michiel Wories Blog: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx
Please, if you want to understand how this work:
1. Try to build a Virtual machine with the OS you’re currently familiar(Windows XP/Vista/7),
2. Make sure all Windows Update are loaded,
3. Make sure PowerShell is upgraded to V2,
4. Install SQL Server 2008 or the latest R2.
5. Then, use the Michiel blog to copy/paste the SQL provider code to your “Microsoft.PowerShell_profile.ps1”.
Or, you can skip steps 1 –> 3, and use the Michiel blog to copy/paste the SQL provider code to your “Microsoft.PowerShell_profile.ps1” so that, every time you start PowerShell Console, you will have access to your SQL Servers (2000,2005,2008) with the following cmdlet:
“PS> cd SQLServer:\SQL\Servername\default_or_InstanceName\databases”
And, use V2 cmdlets:
PS> Send-MailMessage -to "mtrinidad@Company.com" -from "SQL2k8r2@Company.com" -Cc "mtrinidad@Company.com" -subject "SQL Agent Job – Test PowerShell is done!!" -Body "I am done… the system is available." -SmtpServer ‘PostOffice.Company.com’
Look for the location of your user profile folder for “PowerShell”:
Open the PowerShell profile file with your favorite PowerShell Editor: (here’s Microsoft PowerShell ISE)
Here’s what you can expect to see after copy/pasting the SQL Provider code:
Sample of the PowerShell and SQL Server result:
Now, there some things to keep in mind:
1. The Microsoft_PowerShell_Profile.ps1 loads the same script for both 32 and 64bit PowerShell. So you will see a message like to one below to let you know that my 32bit version won’t load my 64bit SQL snapins/provider.
2. Also, when you connect to a SQL Server through the SQL provider, the connection isn’t persistent. So, you need to close/open an new PowerShell session.
3. Keep in mind, this SQL Snapins/provider was meant to use for SQL Servers 2008 moving forward. Due to the different SQL builds, they aren’t backward compatible. But thanks to SMO, for most of the time when connecting to other versions, you will get most of the results back to you.
4. When creating a SQL Agent job adding a PowerShell script to a new step. Make sure to use V1 code. For now, use V2 scripts, you will have to add cmd to call “PowerShell c:\MyScriptHere.ps1” format and run/scheduled the job.
When you start using the combination of SQL Snapins/provider with PowerShell V2 then you are extending your SQL Management skills with PowerShell scripting.
1.Michiel Wories on SQL Snapins/Provider: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx
2. Buck Woody on SQL Server and PowerShell: http://blogs.msdn.com/buckwoody/
3. About SQLPS scripts: http://blogs.msdn.com/sethus/default.asp