Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!).
But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it’s not a SnapIn for PowerShell, but it turns out it is. We ran:
Get-PSSnapin -reg
…and got the following results:
Name : SqlServerCmdletSnapin
PSVersion : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.Name : SqlServerProviderSnapin
PSVersion : 1.0
Description : SQL Server Provider
So then I ran:
Add-PSSnapin SqlServerProviderSnapin
Add-PSSnapin SqlServerCmdletSnapin
And now I have the full SQLPS functionality in my usual PowerShell window. Lovely. And by putting this into a PowerShell console file, I can have it load up automagically for me. SqlServerProviderSnapin gives me the SQL: and SQLPolicy: drives, whereas SqlServerCmdletSnapin gives me Invoke-Sqlcmd for running queries.
<?xml version=”1.0″ encoding=”utf-8″?>
<PSConsoleFile ConsoleSchemaVersion=”1.0″>
<PSVersion>1.0</PSVersion>
<PSSnapIns>
<PSSnapIn Name=”SqlServerProviderSnapin” />
<PSSnapIn Name=”SqlServerCmdletSnapin” />
</PSSnapIns>
</PSConsoleFile>
I’m still not convinced about the merits of using PowerShell to manage SQL Server, but as more Windows Admins adopt PowerShell as their preferred scripting language, I think it’s worth having it as an option.
This Post Has 12 Comments
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
http://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.
But ‘Invoke-SqlCmd’ comes with the SQL PS Provider, so you don’t need to roll your own.
And for backup times, you don’t need to go back into T-SQL, you can just do it from SMO directly:
PS SQL:rob-pcdefaultDatabases> gci | select-object name,lastbackupdate
And if you need to make SMO connections, you can. I guess it’s no different really to using invoke-sqlcmd.
Of course, with SSMS 2008, you can do Local Server Group queries, which makes this much easier to do using T-SQL directly.
Rob
So do you need to install SQL 2008 or can you just copy the SQLPS.exe to your local machine?
Sorry Steve, I actually haven’t tried this! I doubt it’ll work, but please try and let me know!
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.
Goodwin – there are some great books around. Pop down to a bookshop and glance through a few to find one that suits your learning style. But also read blogs like blogs.msdn.com/powershell to stay learning!
Aaron – I’m not sure, because I don’t run Exchange locally. However, if there’s a PowerShell provider at all (through this Exchange Shell you mention), you should be able to see if it’s treated as a snapin using “Get-PSSnapin -reg” like Darren and I did for the SQL stuff. And post back here to let me know how you go.
Thanks for this post.
For the TOTAL newbie, when I type:
get-pssnapin -reg
I receive:
Name : SqlServerCmdletSnapin100
PSVersion : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.
Name : SqlServerProviderSnapin100
PSVersion : 1.0
Description : SQL Server Provider
Then, I should do (based on the “Name”):
Add-PSSnapin SqlServerProviderSnapin100
Add-PSSnapin SqlServerCmdletSnapin100
By the way — I will also provide a document quotation on why use SQLPS at all if anybody could simply add the PS PlugIn. SQLPS is a “Power Shell Mini-Shell”:
“By default, sqlps runs with the scripting execution policy set to Restricted, which prevents running any PowerShell scripts. You can use the Set-ExecutionPolicy cmdlet to enable running signed scripts, or any scripts. Only run scripts from trusted sources, and secure all input and output files using the appropriate NTFS permissions.”
from http://msdn.microsoft.com/en-us/library/cc281962.aspx
Powershell is, well, powerful, and a main concern for a SQL Server admin is inadvertently running something against a shared-database environment.
SQLPS is therefore the default window when right-clicking an object in SSMS.
The link I referenced provides a script for adding SQL Server snap-ins to any PowerShell environment (it is more than just three lines since it includes conditional registration, for example). Also provided is a script to load SMO only (if, for example, the intention is to port to .NET and not use the SQL provider).
Yes Mark, you’re correct. SQLPS isn’t without its uses, but all too often I want to hook into slightly more than what I have available.
And I’m often running PowerShell anyway, which makes the snapins way more useful.
Rob