I am going to be doing a lot of stuff with Powershell in the coming weeks, and to be honest, my Powershell skill are pretty weak.  OK, very weak. I also am all jazzed about trying to do some work with source controlling my databases after SQL in the City (whether I end up using Red Gate’s tools or now).

So, for my first task, I want to script out all of my database objects.  Simple enough, and there are some examples of this stuff on the web already, including Aaron Nelson and Allen White’s examples.  But I want to learn something here, so cutting and pasting is not the way I want to go.

Step 1 is to connect to the database.  Simple, right?  I fire up my Powershell ISE, and enter

 

Hmmm.  Ok, I am not a programmer.  I do know that one of the selling points of PS is that it can use any .NET assembly.  Apparently you need to load the assemblies first.  By default, PS has no clue what SQL is, so you need to load the library so PS can talk the language.

 

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

So, I run that, and try and connect again, and run the proper commands above, and there are no errors.  Progress. 

What does the command do?  In PS, if it starts with $ it is a variable.  So $sqlServer is a variable.  So, a new object is created (“new-object”) of type “Microsoft.SqlServer.Management.Smo.Server”  with an instance of “local”.  I could have changed that to another SQL server that my windows account has access to.  One thing I need to figure out is how to connect using SQL Authentication.  But for now, baby steps.

So, I have a SQL object, named $sqlserver, that I can apply methods to or read properties from .  $sqlserver.GetActiveDBConnectionCount, I get more confusion.  But PS is trying to help me.

It is telling me that I need to add a parameter of type string with a dbName.  I know I have a database named Admin on my server, so I enter the following, and I find out that I have 0 connections to this DB.

 

So, what kinds of properties can we get?  All sorts of things.  $sqlserver.VersionString will return the version and build of my server.  If I enter $sqlserver.Databases, I get….   Whoa…… that’s a lot of info. 

We will come back to that.  For now, try $sqlserver | Get-Member.  That will list all of the events, methods, and properties associated with the variable $sqlserver. 

Keep in mind, if your Windows account has admin privilege’s on this SQL server, you can do bad things.

References

http://blogs.technet.com/b/heyscriptingguy/archive/2009/05/26/why-should-i-use-windows-powershell-with-sql-server-2008.aspx