There are a few that I make heavy use of and are pretty common, but I’ll give a brief on the major ones just for a CYA.
I don’t know what I’d do without it! The main thing that I love about PowerShell over VBScript is that I don’t have to do a ton of object building, I just jump in and start using them. SQLPS lets you do that with SQL, where you used to have to run code similar to this:
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) $MyServer = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) ‘HOME’ $MyDataBase = new-object (‘Microsoft.SqlServer.Management.Smo.Database’) ($Server, “MyDataBase”)
Now all you need to do is run Import-Module SQLPS and it unleashes the power of all the Cmd-Lets. To use SQLPS, you’ll need to install these three items from the link above:
- Microsoft System CLR Types for Microsoft SQL Server 2012
- Microsoft SQL Server 2012 Shared Management Objects
- Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012
Very similar to the plug-in that you can get from Microsoft for AD management, but with a lot of extra switches and parameters to help simplify command syntax. You can find the complete wiki here.
There are more that I’ve made use of here and there and I’ll add them to this list as I post scripts that use them.