THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Microsoft Delivers Full Suite of SQL Server PowerShell Cmdlets

We’ve all been waiting several years for this, and finally it’s here! Coinciding (approximately) with the release of SQL Server 2012, a new Feature Pack has appeared on the Microsoft web site that adds a full suite of PowerShell cmdlets for DDL and other functions. This means that, at last, we can do things like fully-featured SQL deployment scripts without all the (severe) limitations of T-SQL, such as primitive use of variables, flow control, exception handling.

Taking a cue, finally, from the community project SQLPSX, the SQL Server team seems to have designed the new library with ease-of-use in mind, thankfully concealing a lot of the complexity of using raw SMO objects through a language like C# or PowerShell.

Here’s a teaser showing just a few of these new offerings:

New-Table –Schema dbo –Name Accounts –Columns $myColumns

Remove-Table –Schema dbo –Name Accounts

New-Index –Table $myTable –Columns $myIndexedColumns

Set-Index –Name dbo.Accounts.idxBar –Columns $myRevisedColumns

New-Procedure –Name Foo –ScriptBody $sqlcmd

The list goes on! This will make deployment of changes to existing databases so much easier. No more insanely complicated T-SQL scripts with brittle Dynamic SQL and hazardous error handling to manage complicated flow control like “Create this object if it doesn’t exist already.”

And all you admins aren’t left out in the cold either – check out the fresh cmdlets for managing SQL Agent jobs, and the incorporation of Agent into PBM!:

New-AgentJob –Name MyJob –Type PowerShell –ScriptBody c:\scripts\foo.ps1 –Schedule $MySchedule

Set-AgentJobSchedule –Name “Daily at 3:00 am” –StartTime “03:00” –Enable

Set-ServerProperty –MaxDop 4 –MaxMem 8000GB

I am SO excited, and this is amazing, but I haven’t had a lot of time to explore all the options. I hope to really dig in after reading everyone I else’s April Fool’s posts. Until then, you can download this feature pack yourself (probably) at: 

Edit April 2: Please note if you come back to this page later, this was an April Fool's joke. There are a handful of new cmdlets in SQL Server 2012, but nowhere near this level of support.

Published Sunday, April 1, 2012 10:20 AM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Guido said:

This download link doesn't work, the correct one is here:

April 1, 2012 2:16 PM

Ismail Al Bulushi said:

Is there any documentation for these new modules and snapins? Can't seem to even load them properly.


April 2, 2012 10:35 AM

a.m. said:

Hm, maybe you should have ended the post with an example of a really cool one that would go through and automatically tune all of the stored procedures in the system :-)

April 2, 2012 12:40 PM

merrillaldrich said:

Ooo, good one. I'm sure that'll be in SQL 2014 :-)

April 2, 2012 12:45 PM

AndyMoore said:

April Fools jokes are considerably less funny when you see them linked on the 10th. I was so excited too... *cries*

April 10, 2012 9:06 AM

Davos said:

This wasn't funny. It's just a great idea. The joke is that we don't have a full suite of SQL cmdlets

May 21, 2012 10:12 PM

Leave a Comment


This Blog


Privacy Statement