Displaying a list of tables with their rows count:

1. You need to have installed an instance of SQL Server 2008 (or 2008 R2) in order to use SQLPS.

2. To connect to another SQL Server remotely, you need to make sure the server(s) are properly registered in your SQL Management Studio (2008/2008-R2). Look in the *“Registered Servers” pane.

*Hint: You should be able to query SQL Server 2000, and 2005.

3. To start using SQLPS, there’s three ways to open it:

• a. In the Windows Start/Search box, typing SQLPS and then Enter.

• b. SQL Management Studio, right-click on any of the objects and look for “Start PowerShell”

• c. Use your User PowerShell Profile to load SQLPS automatically (follow Michael’s Blog to learn how: http://www.idera.com/richardsworld/post/PowerShellPlus-and-SQL-Server-2008-SQLPS.aspx )

4. Start SQLPS session:

5. Check your SQL Registered Servers:

6. Here’s a very useful one-liner cmdlet to get tables –rowcounts and generate some output:

The first line use the “CD” to change directory to the remote server tables folder. Then, use the “dir” to list all tables. If you need to find more information about that folder then use the “Get-Member” (or the alias “gm”) so you can see that properties and methods you have available at your finger tips.

Example, use the following approach to save the results on a variable and then use “Get-member”:

Then, using the Cmdlet “Select” (short for “Select-Object”), you can work with the properties: “Name” and “RowCount” to be displayed in the PowerShell Console:

dir | Select name, rowcount …

Results:

Have fun with SQLPS!!

Microsoft PowerShell team have only giving a more complete PowerShell and including a scripting editor included in their latest OS’s.  But now, there’s two interesting tools emerging for those venturing deeper in PowerShell scripting:

1. “PowerShell Cmdlet and Help Designer” by the Microsoft PowerShell Team:

Brief description:

The Cmdlet Designer makes it much easier for teams to concentrate on the design, naming, and consistency of their cmdlets, while also guaranteeing name registration and collision avoidance across a project.

Download at: http://cmdletdesigner.codeplex.com/

2. “PowerShellpack” by one the Microsoft PowerShell Team member – James Bundage:

Brief description:

Windows PowerShell Pack contains 10 modules to help supercharge your Windows PowerShell scripting. The PowerShellPack lets you write user interfaces in PowerShell script, manage RSS feeds, schedule operating system tasks, and much more.

Download at: http://code.msdn.microsoft.com/PowerShellPack

Good job guys!!

Special Thanks to Andy Warren for allowing both me and Chad to present our PowerShell SQL Part 1 and 2 at this Awesome event.

Here’s some pictures:

View Full Album

Here’s downloads for both sessions:

1. PowerShell and SQL Server part 1: http://cid-a034d6a0ddc4e64e.skydrive.live.com/self.aspx/SQLSaturday21%5E_10172009%5E_Demo/SQLSaturday21%5E_10172009.zip

2. PowerShell and SQL Server Administration part 2: http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!562.entry

Here’s THE PHOTO of the Florida PowerShell Power Leaders ( Only SW Florida – Jeff Truman is missing in this picture ):

From left to right: Buck Woody (MS Seattle, WA), Ron Dameron (Tampa), Chad Miller (Tampa), and me – Max Trinidad.

Thanks to all attendees and organizers for having us!!

FLPSUG - Florida PowerShell User Group

Starts:
Thursday October 29, 2009 at 6:00pm

Ends:
Thursday October 29, 2009 at 8:00pm

Event Type:
Training/Seminar

Region:
Miami/Fort Lauderdale Area

Location:
New Horizons
100 S Pine Island Rd
Fort Lauderdale, FL 33324 US

Price:

Website:
http://www.flpsug.com

Industry:
computer software

Intended For:
developer, system administrator, database administrator, DBA, and any one who want to now about automation technology.

Organization:
Florida PowerShell User Group

Maximo Trinidad, Microsoft MVP, is the Florida PowerShell User Group's founder and leader. He is truly passionate about sharing his knowledge of PowerShell with others.

Meetings: tentative on 3rd Wednesday of every month (change of dates are possible)

Website: http://www.FLPSUG.com

Blog: http://max-pit.spaces.live.com

Twitter: MaxTrinidad

From one of the MVP’s:

Windows isn’t just about the GUI. Starting with Windows 7, you have built-in access to PowerShell version 2, an object-oriented scripting language and command shell. Please join PowerScripting Podcast hosts Jonathan Walz and PowerShell MVP Hal Rottenberg as they interview Distinguished Engineer Jeffrey Snover on launch day! Jeffrey is the chief architect responsible for PowerShell at Microsoft, and he’ll be covering what’s new with the tool and why every system administrator on the planet needs to be using it. If you’ve never attended PowerScripting Live, you are missing out on a great time. The show will be streamed live via Ustream, and viewers can chat with each other, as well as submit questions for the guest.

· When: Thursday, October 22nd, 9:30 PM EDT (GMT-4)

· Where: PowerScripting Live on Ustream, and follow the blog and podcast atPowerScripting.net

Please join us in this GREAT DAY!!

Thanks Hal for all the hard work!!

Get on the PowerShell bandwagon!!! Don’t be left behind old technology!!

Here’s some Microsoft site that will provide you with more information about PowerShell to get you going:

And, there’s a lot out there!!

First, Special THANKS!! to the Naples .NET Community/Organizer John Dunagan, and Microsoft Developer Evangelist Joe Healy for allowing to get all my session approved.  http://codecamp.swfldev.net/Schedule.aspx

View Full Album

Meet the Florida PowerShell presenters:

Max Trinidad, and Chad Miller.

So, It is possible...  Yesterday, I gave three hour sessions about PowerShell, and co-host the fourth one with Chad Miller.  So, in total, it was a 4 hour PowerShell track.   I had the same people stay for most of the sessions.

I created and presented the following topics:

• Title: Working with Access in PowerShell V2. (Download All 3 Presentations)
  Description: Check out what you can do with Access and PowerShell. I will give you some samples that might help even in your VBA coding and managing your Access databases. 
• Title: Using Namespaces and Embedding .NET languages in PowerShell V2.
  Description: This session will show you the ability to extend the PowerShell scripting from its basic use. Using .NET namespaces and embedding C#/VB into your script. It doesn't matter the level of experience.
• Title: PowerShell and SQL Server (Part 1 of 2).
  Description: See how you can use SMO and the SQLPS (PowerShell mini-shell) to manage your SQL Servers from your console. This will increase your productivity. Then, make sure to follow the second part by Chad Miller - PowerShell and SQL Server Administration. This is a part 1 & 2 most see sessions.

And, Chad Miller presented

• Title: PowerShell and SQL Server (Part 2 of 2). (Download Presentation)
  Description: In this session we will look at automating common SQL Server tasks through Powershell. An overview of the CodePlex project SQL Server Powershell Extensions will be provided. Specific topics covered include SSIS administration, replication/agent monitoring, and building security auditing reports.

Also, we had a special visit from Alex Reidel (CTO) from Sapien Technologies, who distributed some Scripting Tools to all attendees:

One of our FLPSUG Sponsors making presence - Sapien Technologies, Inc.

Thanks Alex for your support.

It was a great opportunity to see how PowerShell audience is on the rise.

VERY IMPORTANT...  During this event, I had a person (Jeff Truman) who want to start a PowerShell User Group in Naples/Fort Myers area.  AWESOME!!!

I just wanted to share the news.  I will blog more next week and post some pictures.

And very excited to be part of the MVP program.

Showcasing Windows PowerShell

PowerShell Means “Powerful Automation”!
Announcing a new webcast series for both Developers and IT-Pro’s.

PowerShell is a Windows management technology designed for ease-of-use by both system administrators and application developers.  PowerShell Version 2 (V2) is available with both Windows Server 2008 R2 and Windows 7 as well as previous Windows releases via an optional update.

For Developers specifically, Windows PowerShell in combination with the Windows Management Infrastructure (WinRM, WS-Management, WMI) provides a great way to automate server hosted solutions.   For example, if you implement all your administration logic via PowerShell, then layer the MMC GUI over the top (i.e. MMC calls PowerShell to get the work done) - you will have given your Enterprise customers the absolute best of all worlds; GUIs, scripting, and delegated, remote automation.

PowerShell V2 introduces many new features including remote sessions, an integrated script environment, debugging tools, and much more. 

Start your video tour of PowerShell V2 via MSDN Channel9 and TechNet Edge.  Find reusable scripts and techniques at the PowerShell Script-Center.  Subscribe to the RSS feeds at both the PowerShell and the Windows Management team blogs.  Get demo scripts from MSDN Code Gallery.

Download and share the new Windows Server 2008 R2 Developer Training Kit with PowerShell HOLs!

We hear it from the main source @JSnover… RC (Release Candidate) PowerShell V2 for Windows Server 2003 and XP are available:

“XP and W2K3 RC versions of PS V2 and WINRM are now available for download http://tinyurl.com/mqnkm6 - SNOOPY DANCE!”

Let’s all do the Snoopy Dance!!!

Applies To: Windows Server 2008 R2

System Center Virtual Machine Manager 2008 R2 Cmdlet Reference

http://tinyurl.com/lm2rd2

WHat's New in Windows PowerShell Cmdlets for Roles and Features

http://tinyurl.com/knnyjy

Applies To: Windows Server 2008

System Center Virtual Machine Manager 2008 Cmdlet Reference

http://tinyurl.com/l8ngroo

When available… Look at the recorded livemeeting TechNet Webcast “Using Windows PowerShell with Hyper-V and Virtual Machines (broadcast on 09/10/2009)

Thanks to Marco, Hal, Darin.

Here’s my experience installing my Windows 7 RTM which I was very excited to do.   After experiencing an almost perfect Beta and RC then it was a little more work with RTM.  Please, don’t get me wrong but I put myself in the spot.  

My headaches after installing Windows 7.   I had some roadblocks when putting back all my applications, 

So, this is what I learn from my own mistakes:  (Is not Microsoft fault!)

1. And, this is common sense, Create backup image of your system and/or backup your files on another drive

2. In my case again,  I couldn’t upgrade from RC to RTM but the good new is that, if you on Vista, then you will be able to upgrade.  So I did a clean install.

3. I had problems installing applications,  they will failed no matter what you do.   My problem was cause because I didn’t connect my machine to the internet.  So, this setup errors was cleared after my connected to the internet and download the Windows Updates.

4. SQL Server 2008 RTM (not SP1) installation failed on my first try.  Then, I decided to install Visual Studio 2010 (which include SQL Server 2008/Express).  Then, I was able to Install SQL 2008.

5. I had problems Installing my Windows Live components (mail, writer, meeting) the whole packages failed due to a program conflicting behind the scene.  Found the program and did an uninstall to clear the condition.  The program was one of the components in Visual Studio 2010 “Tools for Application” that somehow (in my case) was affecting my Windows Live installation.

So, after I clear all my application installation issues, then I was to complete loading all my programs.

Now, for those who are still experiencing SQL Server 2008 installation issues, please check the following blog:  ( it may help you ) http://bit.ly/g6iSZ

I truly love my Windows 7!!!… its been running Smooth!!!

Thanks to all SQLSaturday#16 organizers, fellows SQL Speakers from all over Florida for coming over, and one guy in particular that brought his wife from South Carolina just to speak the conferences (Chris Eargle).  Both Chad Miller and myself appreciate to have is participate in a Part 1 and part 2 sessions about PowerShell and SQL Server.  We all have a good time.

View Full Album

Here’s our “PowerShell and SQL Server” part 1 and 2 sessions (Demos and Presentations are included):

Part 1: PowerShell and SQL Server (intro) – Part 1 by Max Trinidad (Port St. Lucie):

Part 2: PowerShell and SQL Server Administration – Part 2 by Chad Miller (Tampa):

Chad and I will be presenting our 1 – 2 session at the next SQLSaturday #21 in Orlando – October 17th.

This was a great experience and very successful.

Forgot to mention...  Special THANKS to Quest - PowerGUI and Software FX - PowerGadget for Providing the giveaways for all our attendees which was distributed in both PowerShell session.

Thanks for your support.

Thanks to the Microsoft PowerShell and Springboard/Windows 7 Team, Windows 7 RTM is out with PowerShell v2 RTM included.  With a great tools comes greater responsibility.  PowerShell v2 comes its more powerful than ever and comes with lots of tools (…such as remoting, ISE, modules, advanced functions, enhanced WMI support, and a variety of new cmdlets…) that will help your servers administration.  Don’t forget, you can extend your PowerShell scripts to be more powerful thanks to .NET Framework.

PowerShell Team blog: http://blogs.msdn.com/powershell/archive/2009/07/23/windows-powershell-2-0-rtm.aspx

Checkout the Windows 7 RTM Springboard series and more information: http://technet.microsoft.com/en-us/windows/dd361745.aspx

Job Well DONE Microsoft.

Yes, finally is out.  I like “PrimalForms Community Edition” but the new “PrimalForms 2009 v1” will make you fall in love for it.  This new version with the added editorm and more features will integrates with their  “PimalScript Studio”… its the best companion and tool for any PowerShell Scripters/developers. 

Here’s more resource links:

Sapien Technologies: http://www.primaltools.com/products/info.asp?p=PrimalForms

The Lonely Admin: http://jdhitsolutions.com/blog/2009/08/04/primalforms-2009-now-available/#utm_source=feed&utm_medium=feed&utm_campaign=feed

This is a most have tool.

Just found this link.  Check it out!!   PowerShell is moving along.

Active Directory Powershell Blog : Use Active Directory Powershell to manage Windows 2003/2008 DCs

Lets get right to the point…  Download this great from Sapien the “PrimalForms Community Edition”.   Its free and with a little extra work, you can add value to your scripts.  In this sample I’m adding a Windows GUI to one of my demo SQL SMO script to backup a database. 

Here’s the original script:

But, by adding a GUI interface and with some extra work, I can make it more flexible.  This way I can reuse this script to backup any database from any server.  Of course, keep in mind, you need use the right SMO assembly for this script to work.  But, for now, we only going to use “Microsoft.SqlServer.Smo” and not the "Microsoft.SqlServer.SmoExtended".

I build my simple Windows GUI so I can extend my script so I can type my required parameters to do my database backup. So I use PrimalForms:

I need to create a GUI with three text fields to pass some information and a button to execute my code.  During this process there will be some very basic validation.  Here’s how the form will look like:

After the form is completed, then at the tool bar you click on the “Export PowerShell”:

This option will allow you to build the forms code so you can send it to three areas:

1. Sapien “PrimalScript” Editor.
2. Send to Clipboard.
3. Send to a file.

After the export, make sure you name the file.  Now, with my editor,  I need to look for the section of the “Button_OnClick” so I can add both the validation and the SMO backup code.

# Simple textboxes validations look for empty values
    if ( $SQLServer -eq ""){
        $Allfields = "1. "+$SQLServer
        [system.Windows.forms.MessageBox]::Show($Allfields+"`r`n"+"Invalid entry or blank not allow!!")
        $Pass1 = $false;}
    else {
        $Pass1 = $true;
    }
    if ( $DbName -eq ""){
        $Allfields = "1. "+$textBox1.text+"`r`n"+"2. "+$DbName+"`r`n"
        [system.Windows.forms.MessageBox]::Show($Allfields+"`r`n"+"Invalid entry or blank not allow!!")
        $Pass1 = $false;}
    else {
        $Pass1 = $true;       
    }

    if ( $BkPathName -eq ""){
        $Allfields = "1. "+$SQLServer+"`r`n"+"2. "+$DbName+"`r`n"+"3. "+$BkPathName
        [system.Windows.forms.MessageBox]::Show($Allfields+"`r`n"+"Invalid entry or blank not allow!!")
        $Pass1 = $false;}
    else {
        $Pass1 = $true;
    }

    # If pass validation then execute the backup and verify that the backup file exist
    if ($Pass1 -eq $true){
        Add-Type -Assembly "Microsoft.SqlServer.Smo" | Out-Null
        #[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
        $svr = New-Object Microsoft.SqlServer.Management.Smo.server $SQLServer;
        $bkp = New-Object Microsoft.SqlServer.Management.Smo.Backup;
        $sqlBkupFullpathName = $BkPathName+"\x"+$DbName+".bak"
        $bkp.Devices.AddDevice($sqlBkupFullpathName, "file");
        $bkp.Database = $DbName;
        $bkp.Action = "Database";
        $bkp.Initialize = $true;
        $bkp.SQLBackup($svr);}
        if (Test-Path $sqlBkupFullpathName){
            [system.Windows.forms.MessageBox]::Show("Backup $sqlBkupFullpathName is done!")
        }
    else {
        $Allfields = "1. "+$SQLServer+"`r`n"+"2. "+$DbName+"`r`n"+"3. "+$BkPathName
        [system.Windows.forms.MessageBox]::Show($Allfields+"`r`n"+"Backup Error...")   
    }
}

Again, this script still have room evolve more and the you can keep extending the power of PowerShell with .NET framework.  A simple sample is the use of “… [system.Windows.forms.MessageBox]::Show(“Test message here”) …” so that a windows message box will popup.  Now, we finally completed our simple database backup GUI application.

Make sure you get SMO and PrimalForms installed and feel free to download the script: MyDbBackup.ps1

(Please rename file to a *.ps1)

For more information:

PrimalForms Community Edition and other tools - http://www.primaltools.com/downloads/communitytools/

Microsoft SQL Server SMO downloads:

For SQL Server 2005: http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

For SQL Server 2008: http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

Sign up and join us in our new location:

For registration, click here: http://www.flpsug.com

Yes!!  Today I got the news that New Horizons will host our monthly meeting starting September 16th.  I will post more information later.

I’m very excited and this is a great opportunity to expand our PowerShell community in our area.

Thanks to all our sponsors and member for your support,

Max Trinidad

:)

Yes, everyone is invited to attend this coming August 8th to SQLSaturday #16 to join us, Chad Miller (West Coast)– creator of CodePlex “SQL Server PowerShell Extensions” and myself (East Coast) to our PowerShell and SQL Server Sessions.   Two great sessions with brand new material, covering SMO, SQLPS, and new version of SQLPSX.

Click here for SQLSaturday #16 schedule

Max Trinidad Blog and Florida PowerShell User Group site

Chad Miller Blog

SQLSaturday #16 South Florida - Windows Live