Rob Farley

I hear PowerShell is an improvement over VBScript, but I'm impartial either way.  I wonder if there will be any improvements on this in the RTM of SQL Server 2008?

**************

Nico del Castillo

Microsoft 2008 Joint Launch Team

www.microsoft.com/2008jointlaunch

Thanks for figuring out the PSSnapins for SQL 2008.  I wasn't looking forward to having 2 different flavors of Powershell open all the time.  This makes it easier to integrate with my existing tools.

In terms of administrating SQL using PS.  It is almost a must for a large environment.  Just try to use SSMS to figure out when the last backups happened to all databases on all servers.  It works ok on 1 server, but as the number goes up, so does the amount of time it takes.  With a script/CmdLet you can do things like the following:

gc .\SQLServers.txt | % { Invoke-SQL.ps1 -Server $_ -InputFileQuery .\BackupStatus.sql }

Now you have a list of all backups on all servers and can go from there (After you've made the BackupStatus.sql script).  This also makes a huge difference if you have a lot of databases.  For example at last count I was checking the backup status of over 1100 databases using PowerShell.  Try that with SSMS.  You can do it, but it takes DAYS.  Currently it takes me about 3 minutes to run the script and a few more minutes to review the results.  Now you could do this with other scripting languages, but it isn't as easy to get access to the underlying objects/rows/columns from the command line.  

Other Possible Uses:

Invoke-SQL -S Dev1 -Q "select @@servername as 'Server', getdate() as 'ServerTime'" # Check time on server

Invoke-SQL -S Dev1 -Q "exec sp_who2"  # what's running on a server

Invoke-SQL -S Dev1 -Q "exec sp_helpdb" # Get Name and Size of databases

# Get number of blocked processes for one server or for all servers.

Invoke-SQL -S Dev1 -Query "select @@servername as 'Server', count(*) as 'Blocked' from master.dbo.sysprocesses where blocked <> 0"

gc .\SQLServers.txt | % { Invoke-SQL -Server $_ -Query "select @@servername as 'Server', count(*) as 'Blocked' from master.dbo.sysprocesses where blocked <> 0" }

#Insert data using a hashtable as the source for parameters.  My Invoke-SQL.ps1 script adds parameters based on the hashtable passed in.

Invoke-Sql -S Dev1 -Database Test1 -Query "insert ServerList (ServerName, IPAddress) values (@ServerName, @IPAddress)" -Parameters @{'@ServerName'='ProdWeb01';'@IPAddress'='192.168.0.134'}

These are a few of the things I use almost every day, although my scripts are more complex.  Hopefully it will give you an idea of what you can do with powershell and why it's usefull for administrating SQL Servers.

p.s. The Invoke-SQL.ps1 script in the examples isn't available online yet.  It's something I cooked up myself.

Oh, the things you find when you are writing a book. Now, the problem is to spend less time 'finding'

Pingback from  MSDN Blog Postings  &raquo; SQL Server 2008 PowerShell SnapIn

So do you need to install SQL 2008 or can you just copy the SQLPS.exe to your local machine?

As you may know, we’ve created a SQL Server 2008 provider for PowerShell. With this very cool feature

Hi Rob and everyone!

Great article, I DBA managing just over 60 SQL Server 2000/2005 Instances, supporting over 300 databses and it seems my day will be much more productive if I can script in PowerShell.

I know the basis in VBScript via WSH, so some knowledge base on my part to work with, but will like to take on PowerShell.

So, Please where would you suggest I start - books and web resource etc I could use to get up to speed.

Thanks

Goodwin

In my opinion, the best book on Powershell is and remains Bruce Payette's Powershell in Action. Also very good is the Windows PowerShell Cookbook by Lee Holmes.

I just started reading Pro Windows PowerShell by Hristo Deshev, and that one looks pretty good, too.

If you only buy one book, however, get Powershell in Action.

Cheers

Does anyone know if this works for the Exchange 2007 Command Shell?  For those of us that admin multiple platforms and applications, the prospect of PS bloat is demoralizing.

Regular readers of my blog will remember my post about how to get PowerShell for SQL into your regular

Pingback from  PowerShell changes in SQL Server 2008 RC0