SQL Server Reference Guide

We're continuing our series on scripting with PowerShell. We're getting closer to the "work" part of the script, where I'll explain the SQL Server commands you can use to manage your server. You'll recall from the requirements I laid out earlier in the series that our ultimate goal is to backup a database, compress the backup file, name the file with a date discriminator, copy the backup and reapply it on another server. We're going to be able to do that, all from a single script, right from the command line.

Note: There's a broader overview of scripting here where I describe the difference between a scripting language and a full programming exercise.

So far I've explained how to get and install PowerShell, and then we've worked our way through the basic concepts to error handling and then last week a logging process. But to make the scripting exercise truly valuable, we need to add in one more construct to our script. We need to be able to make the file run against more than one server, and more than one database on each server. No, that wasn't in the original requirements, but if we build the script with extensibility in mind, we can use it now and later.

There are a few ways to do this, but they all revolve around nested loops. Again, if you're familiar with coding already, you know this. If not, you've already learned what you need to know in this series. Previously we used a "foreach" loop, in this format:

foreach (some_item in some_group_of_items) { do_something }

So now we'll extend that concept just a little. We're already in a some_group_of_items, and we're in the do_something part. We'll just add another foreach construct right in the middle of that, like this:

foreach (some_item in some_group_of_items) {

do_something

foreach (some_item in some_group_of_items) { do_something }

}

This isn't too difficult to understand. We're just placing a loop within a loop. This is called "nested loops", and you can have a lot of them. Normally you don't want to do dozens of loops, because you can get confused about the chain of things that are happening, but one or two "levels" of nesting are normal.

So that was pretty easy - but the bigger issue is where do the some_item and some_group_of_items come from? We could set up variables, or we could pass in the names of servers and databases at the command line, but this isn't very efficient either.

What we need is a way to store the names of the servers and databases outside of the script. It turns out this is a problem that has been solved since the early days of computer science. You just create an external file with the names of the servers and databases, and then read that file in to the script. Once again, you can read the file and make variables out of the server and database names, but it's actually better to use the line-breaks within the files to iterate through the things we want to work on.

We can use a text file or a binary file. Each has advantages and disadvantages. For instance, a text file is easily read and parsed, and it can be edited by almost any operating system, using native commands or simple text programs. On the other hand, those same strengths are also weaknesses. If you want to store connection information in the control file, you certainly don't want it to be read easily. A binary file is more appropriate for that sort of requirement. Also, binary files are faster to work through for large data sets.

In our case, we're going to rely on the operating system to provide the credentials for our database login. That's a very safe choice since a user can even obtain our file, but it won't run for them because the database won't recognize their credentials.

So text is the way to go in this situation. But how do we indicate within the text file that a particular database belongs to a particular server? We could create a file that looks like this:

SERVER1:DATABASE1
SERVER1:DATABASE2
SERVER1:DATABASE3
SERVER2:DATABASE1
SERVER2:DATABASE2

We would then just break each line at the colon, and then read the database. But we leave ourselves open to errors here because we might spell a server name incorrectly since we're repeating it. Not only that, it isn't that easy to read.

Enter the Extensible Markup Language (XML). XML is perfectly suited for this task, since it is built on items (called elements) that next inside each other. In other words, XML can accurately represent a hierarchy, and it's even more suited here because it can contain "children" of data (such as databases in a server) that aren't equal in number, as we have currently listed above.

It gets even better, because XML can be read and edited using many tools. It's still just text, but it's formatted a certain way.

I won't cover XML in detail here, since I've done that in another set of tutorials, and InformIT even has a complete guide devoted to it. What I will describe is the XML file we will use in this tutorial, and how it is laid out.

If you're familiar with XML, you know there is one decision we need to make fairly early in the design of the file. We have two major parts of the file we can use to indicate the server (or parent) and the databases (the children). We can use an element, which is similar to a field, and we can use an attribute, which is like an extended description of the field. In fact, we can use either or both.

Normally I recommend that the XML file use elements for any discrete data component, and attributes for specifics around those elements. Like this:

<?xml version="1.0" encoding="utf-8"?>
<servers>
   <server >serverOne</server>
   <server >serverTwo</server>
</servers>

And that would be an acceptable way to represent the servers. Or, we could use attributes for the name instead:

<?xml version="1.0" encoding="utf-8"?>
<servers>
<server servername = "serverOne"></server>
<server servername = "serverTwo"></server>
</servers>

That works correctly as well. In fact, there are lots of ways to arrange the XML elements and attributes. As long as you parse it properly, in a small file like this one it really doesn't matter, and since we'll be iterating through some lengthy processes, I'm going to use attributes for the server and database names. This will make sense in a moment. Here's the way I've laid out the file for the servers and their databases:

<?xml version="1.0" encoding="utf-8"?>
<servers>
  <server servername = "serverOne">
    <database databasename = "databaseOne"></database>
    <database databasename = "databaseTwo"></database>
    <database databasename = "databaseThree"></database>
  </server>
  <server servername = "serverTwo">
    <database databasename = "databaseOne"></database>
    <database databasename = "databaseTwo"></database>
   </server>
</servers>

There you have it. We've embedded the names of the databases within the servers they belong to, and left them around elements that we can use for more things later.

The other nice thing about using a control file like this is that you can create a front end to the file using another program, and any administrator can just edit the file to have it run on another set of servers or databases. That makes it truly extensible.

There's another advantage to using an XML file here. PowerShell loves XML. It has several ways to open and iterate an XML file. In this example I'll show you the simplest way to do that.

First, copy that XML file from above and save it as a text file in your C:\temp directory on your test system. In PowerShell we'll open the file and treat it like we have everything else – as an object. We simply have to add a qualifier for the variable name, like this:

$doc = [xml]( Get-Content c:\temp\servers.xml )

We've created a new variable, called $doc. We've set it equal to the command to open a file (Get-Content) with a special qualifier – [xm]. That makes this object the contents of an XML file, with all the things that PowerShell knows how to do with an XML file.

Now that we have it, what can we do? Try this:

$doc

You'll see two columns of data – one empty, and the other with the word "servers" in it. Try this:

$doc.servers

Very nice – this shows us the contents of the servers element, along with the servername attribute we set in the file. Now this:

$doc.servers.server

You can see that we're just navigating the XML file as if it were an object, which in fact it is, at least in PowerShell. But if we try this:

$doc.servers.server.database

We stop. This has to do with the fact that we're at the "end of the tree". PowerShell needs to know which server's databases we want to know about. So now let's try this:

$doc.servers.server[0].database

Ah. Much better. You'll notice that PowerShell starts counting at 0, instead of 1. Like a lot of languages, it just does that. We've asked for the databases on the first server (which as I mentioned is really number 0), and we can do the same thing for the databases. We'll see that in a moment.

So now we can get a list of servers, and a list of databases within them. We can detail out the names of the servers and databases we want into loops. Let's put this all together in a simple script. The comments will help you see all these concepts:

# Do some work
# Set the global variable of where we are in the code
$programSource = "Main Block"
 Trap {
# Go to the error handler
 ErrorHandler
# Stop the program.
 continue;
 }

# Set a counter for the servers
# Create an XML document object, read in the file
$doc = [xml]( Get-Content c:\temp\servers.xml )
# First loop for the servers
foreach ($server in $doc.servers.server) 
   {
   Write-host "We’ll connect to server " $server.servername
   # Second loop for the databases
   foreach ($database in $server.database) 
     {

     Write-host "     We’ll work with database: "
$database.databasename
     }
   }

If you run this script, you'll see the output of each server and its databases. In the next tutorial, we'll fold this all in to the main script, and then put the sections to work.

InformIT Articles and Sample Chapters

PowerShell is built on top of the .NET infrastructure, so you can use any of those constructs here. There's a great reference for Event logging in .NET here.

Online Resources

If you haven't run across this reference yet, it's a great resource to have. It's a quick start to PowerShell, and contains almost everything you need to get started.