Hey, Scripting Guy! I am a SQL database administrator, and in the past I have enjoyed using VBScript for many tasks related to my computers that are running SQL Server. What capabilities does Windows PowerShell provide to make working with SQL Server 2008 easier?

– OS

Hi OS,

Ed is neck deep in work for the 2009 Summer Scripting Games. He is sipping a cup of Prince of Wales tea, and listening to some Bach on his Zune (yes, we also suspect Ed gets paid a small remuneration each time he mentions his Zune). Ed has been re-reading his heavily worn limited edition of The Adventures of Tom Sawyer recently (not sure if that fact is relevant or not).

Because Ed does not consider himself to be a SQL Server 2008 guru, he decided to pass this question off to Buck Woody, SQL Server Technical Specialist for the Microsoft Corporation. He is a former Program Manager on the SQL Server team and the author of seven books about SQL Server. (Compared to Buck, Ed cannot even find SQL in the dictionary.)

Yesterday, we explained that Windows PowerShell is a shell, a set of new commands, and a way to script out tasks. But database assistants are a practical lot and demand more. They already have several tools to work with SQL Server. Why learn an additional tool? Because Windows PowerShell does more than just let you type commands into SQL Server. You have the whole Server Management Objects (SMO) library at your disposal—the same library that SQL Server Management Studio uses. Anything that you can imagine doing in SQL Server Management Studio, you can do with Windows PowerShell—in a script.

Let us start where we left off last time and see what we can do with this newly found power. Remember that we learned how to load the SMO libraries, and then we learned how to connect to a server and reference it as a variable:

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
$sqlServer = new-object (“Microsoft.SqlServer.Management.Smo.Server”) “(local)”

Now we have a connection to a SQL Server object, and we can do a lot with that simple variable. We can see all the actions we can perform (known as methods) and the attributes we can list or set (known as properties) of the database object by using variables, piping, and the commands from Windows PowerShell:

$sqlServer | 
get-member | 
MORE

As you page down the screen, you might notice a property or two that would be useful to have. One of them is the number of processors installed and usable by SQL Server. With the SQL Server Instance declared as a variable, we can just ask for that by putting a period after the server name and then adding the property we want to see:

$sqlServer.Processors

That is very cool. We can ask about other properties additionally, such as whether the Instance is running:

$sqlServer.Status

To this point, we have been working with the SQL Server Instance. However, because this is the SMO library, we actually have access to almost everything in SQL Server. Let us say that you are interested in the databases on the Instance. This is somewhat different from what we have done already. You see, the things we have asked for have all been a single value—a property on the Instance in this case. The SMO library has not only a single object such as an Instance that you can work with. But those objects can hold other objects. The Status property we asked for is an example of that. But some objects have multiple children underneath them called collections, and you can tell them by their names. The objects that hold even more objects have an “s” at the end of the name, at least most of the time (Processors is an exception, for example). The databases that the Instance holds are a collection because there are other databases underneath it.

To access the objects in a collection, we can use brackets and the number that refers to it, such as this:

$sqlServer.Databases[0].Size

That is somewhat useful, but there is no real way to tell which database this is. Luckily, Windows PowerShell can also take the name of the database in quotation marks for a more friendly display, such as this:

$sqlServer.Databases[“Adventureworks”].Size

That is better. And what can we see about databases, or any object, for that matter? Well, we can turn back to our old friend, Get-Member:

$sqlServer.Databases | get-Member

But what if an object holds even more objects? For example, an Instance has databases, which have tables. How can we see a property there? Simple. We just keep adding those child objects. In this case, even a table is a collection because it holds columns and so on. Here is an example. Let’s discover how many rows are on a table:

$sqlServer.Databases[“Adventureworks”].Tables[“AWBuildVersion”].RowCount

(By the way, this command works because my default schema is dbo together with this table. More about how to browse other schemas later)

But let us say we want to go even further. We would like to examine all the tables and show the row counts. For that, we will have to use another Windows PowerShell feature: functions. A function in Windows PowerShell is exactly like functions in other languages: It is just the name of the function followed by some parameters usually wrapped in a pair of parentheses. In this case, we want to get information about a group of items. We must have a function that reads a group of items and does work on each item in the group. In Windows PowerShell, we have the foreach function. This is followed by any instructions we want.

The interesting thing is that Windows PowerShell does not require us to set up a variable to walk through the list of items like many other languages. Here is what the format of that command resembles:

forEach (BrandNewVariable in SetOfObjects) {do some work} 

Let us put that to work. We’ll use the forEach() function together with our database reference variable ($sqlServer), the AdventureWorks database, and the tables object to list out all the names:

forEach ($table in $sqlServer.Databases[“AdventureWorks”].Tables) {$table.Name}

Now let us add the row counts to the previous command. To do this, we query the rowcount property from the table object:

forEach ($tables in $sqlServer.Databases[“AdventureWorks”].Tables)`
 {$tables.Name + “: ” + $tables.RowCount}

I think that you have the idea. We have now created a variable connection to a SQL Server object or two, and we can do a lot with that simple variable. We can see all the actions we can perform with that Get-Member cmdlet:

$sqlServer.Databases | get-member | MORE

We have been playing around with the properties on the object. So far, we have been just reading them. But many properties can be set equally. This we can see in the second column of the output. Let us change one of the properties on a sample database. Let us change the AutoShrink setting. In this example, we will see the current value, turn it on and then off again:

Note: We cannot tell you how important it is to do this on a testing database, on a testing system. This test system must be something that you do not mind losing, and that no one else is using. No, this should not hurt anything, but our houses should be worth what we paid for them. But they are not. So stop if you are not on a test system.

$sqlServer.Databases[“AdventureWorks”].AutoShrink
$sqlServer.Databases[“AdventureWorks”].AutoShrink = 1
$sqlServer.Databases[“AdventureWorks”].AutoShrink
$sqlServer.Databases[“AdventureWorks”].AutoShrink = 0
$sqlServer.Databases[“AdventureWorks”].AutoShrink

Of course, this is a fairly simple thing to do. Set a property here and there. How about something that is somewhat more interesting, such as scripting a database? First, let us see whether there is a method (an action) we can perform on the database that sounds somewhat like scripting:

$sqlServer.Databases | 
get-member | 
MORE

There it is in the S’s: Script! Could it be this simple?

$sqlServer.Databases[“AdventureWorks”].Script()

Now, that is just too cool. Hey—we wonder if we could script out multiple databases. Let us use that same loop we tried earlier and see whether we can use it to script out all the databases:

forEach ($database in $sqlServer.Databases) {$database.Script()}

But of course, we need a way to save all this. We can use another cmdlet that is known as Out-File. That cmdlet creates a file on the hard disk, and it has several switches that you can see with the Get-Help cmdlet. All we have to do is add a piping command to the “work” section of our loop, and then we can send each object, one at a time, to a file. Here is an example that bundles up all the database scripts into a single file that is named c:\temp\Scripts.sql:

forEach ($database in $sqlServer.Databases) {$database.Script() |`
 out-File –Filepath c:\temp\Scripts.sql -Append}

But let us say we want to retrieve all the tables in a particular database scripted, instead of just the database itself. We can use another loop to do that. (Warning, this might take some time to run! You can press CTRL+C if you want to stop the script.)

forEach ($tables in $sqlServer.Databases[“AdventureWorks”].Tables)`
 {$tables.Script()}

All we are doing there is walking through the table objects on the database and sending them to the scripting object. But moving through the SMO object model does have some challenges. As you work through these examples, you will undoubtedly encounter them. Can Microsoft just make this a bit easier? Sure, we can. And next time we will show you what we have done. We cannot wait!

Once again, Buck has hit a home run. Oh, yeah, Ed’s Bach prelude is complete, and his tea pot is empty. He has written an absolutely dastardly event for the 2009 Summer Scripting Games. He smiles a crooked little grin and does a passable imitation of Dr. Evil. The Summer Scripting Games will be awesome. Thank you for writing, OS, and join us tomorrow as Windows PowerShell with SQL Server 2008 Week continues. Until then, stay safe. For the latest information about the Summer Scripting Games, follow us on Twitter.

Ed Wilson and Craig Liebendorfer, Scripting Guys