SQL Server Reference Guide

I'm continuing a series on scripting for real-world applications; this time using Microsoft's newest scripting language, PowerShell. If you haven't read the previous entries, you might want to take a look at those before you continue here — I've introduced some concepts that we're now ready to build on.

I've also done an overview of scripting in general, in which I describe the difference between a scripting language and a full programming exercise. Scripting is often command-based, and deals less with formal programming concepts like objects, inheritance, and other advanced terms. In both scripting and programming you're just writing instructions that a computer will carry out. In programming languages the commands you use are more abstracted — they do things like branching off to other parts of the code and including things written in other parts of the program.

Most DBAs know how to write code. You've possibly been to a Computer Science class or two, learned on your own through courses, or just figured it out on the job. Because those last two aren't as consistent as other methods, you might not have learned everything that you need to be a good programmer. But that's normally OK, since batch files or other scripting methods don't have the range of concepts that formal programming languages do. Not only that, you might not code for a living.

There are exceptions, of course. If you've used vbscript, cscript, Perl, or other "scripting" languages you've at least had things like functions available to you. Perhaps your DBA tasks include writing stored procedures, functions or other coding tasks. In that case you may already be familiar with various programming concepts. I've covered some of these concepts in the Programming section of the SQL Server Guide on InformIT. Many of these concepts exist in PowerShell. Up to now, we've been using PowerShell by simply running commands one at a time, and then by stacking those commands in a text file and allowing that to run one line at a time.

But now it's time to put all those concepts to work, and to do that properly we'll have to dive in to programming — a little. Those of you who are comfortable with programming concepts will likely look at this article and think, "what's the big deal?," but for those Database Administrators who have to know everything from Windows to SAN hardware, networking concepts and security, the last thing they want to do is add another thing to learn to the list. It isn't just learning some new technology; it's keeping up with that knowledge when it changes and evolves. DBAs don't often have time or interest in maintaining knowledge they can only use in one place.

Don't worry — what I'm about to show you isn't "wasted" knowledge. What you'll learn here is universal to many programming and scripting languages, and very powerful. In fact, I'm only going to introduce one additional concept to the simple ones you've already learned. And for those of you who are experienced with programming concepts, this is something you already know. And if you've been a DBA for very long, you also already have this concept down.

What I'm talking about is the idea of functions. Functions are simply names in the file somewhere that bundle up some task you want to repeat a number of times. When you need it, you "call" the function by including that name in a line of code. PowerShell interprets this name as a jump, and branches down to the code you've written in the function. When the function is done, the code continues where it left off. Functions can also accept inputs from another part of the code, work on those inputs, and return results or just perform a task using them.

This is a very powerful tool, since it lets you write the code in one place, but use it multiple times. While that isn't very difficult to understand, the real power is in passing information in to the function, and receiving data back out.

We'll need this right away because we want to do two things in our larger script over and over. In fact, you'll often want to do these same steps over and over in every script you write. The first thing we want to do is log events, and the second is to deal with any errors that our script generates. We'll set up our script "framework" to have those two functions built right in, and we can call both of them at any step along the way. We'll build a framework that we can use with any PowerShell script, and we'll fill this one with some meaningful code for this series. You can use this framework with a few changes here and there over and over.

In this series we'll make a script that performs a database backup to a file, compresses the file and includes the date in the name, copies the file, and then restore the file to the same or another server. We'll drive the databases, servers, locations and other variables from an XML file to make it useful, and log the entire process. We'll also perform simple error checking along the way.

I've had to do this kind of thing in the past, and because I needed to easily transport the task to another environment, scripting was the best solution. I've used this for reporting databases, development environments, just about anywhere that I needed a copy of one database in total to another. Even if this isn't a scenario you're interested in using, the concepts hold for just about any task you need to perform in Windows or SQL Server.

So let's get started. As I mentioned in my series on programming, the first thing I do when I'm writing any code is to outline the steps I want to take using comments. That's easy enough, and we'll use single comments along the way, which is the "pound" or "hash" sign: #. I always include the name of the script, the purpose, the author, and the last changed date in the file, along with any special instructions needed to make it work. We'll being with a simple script that forms the basics. If you're following along at home, open a text editor and insert the following text:

# TransferDatabase.PS1
# Backup, Compresses, Copy and Restores a Database from 
# one Server to Another.
# Buck Woody
# Last changed: 07/02/2007
# Requires an XML file called "TransferDatabases.XML" 
#in the current directory
# Logging Process Function
# Error Handing Function
	# Do some work

Now let's take care of the second section, the error handling. We'll actually find errors within the "# Do the work" section, but the results of that error will be handed off to a function to deal with them.

By the way, in formal programming, this isn't always the way you'll handle errors. Most of the time you'll get a very specific error returned to you, and you'll handle each in a different way. For instance, perhaps the backup won't complete because you're out of room on the hard drive where you send it. In that case you might want to try an alternate location, or potentially send you an e-mail or call your pager to take care of the situation. That requires different error-handling steps than when the compression step fails.

In this case, we're only doing the bare minimum, sending the error messages to the log so that we can deal with them later. As I mentioned, this might not the best way to deal with the error, but you should at least have this much handling built right in.

So let's get coding. We'll catch any error in the code and call the function. The first construct is quite simple. You start the work with the word "trap." This means exactly what it says — "Try this, and see if it works. If it doesn't, trap what happened." To make that work, the word trap is followed by a set of curly-braces, as in this example:

Trap {
# Do something if an error occurs.
}

To deal with errors, you have to have some. In fact, you probably already do – you can see them by asking about the error object in PowerShell, which is called $error. Try typing that at the PowerShell prompt and a screen or two of data will probably scroll by. This variable stores the outputs of the Error object in PowerShell. While we won't manipulate that object directly, we'll query its properties to learn more about what just happened, so we can fix the issue.

As I mentioned, you'll most often deal with the problem in particular, but in this case we'll just record the error and stop the program.

So what can we ask the error object? Actually, quite a bit, but these are the ones we'll include:

• CategoryInfo: This is the category of error that occurred.
• TargetObject: This is the object that was in play when the error happened.
• Exception: This is the description of the error that just occurred. It also has properties that we'll query.
• FullyQualifiedErrorId: This is the specific error. Useful when you need to look the problem up on the web.

Since we don't want all errors documented repeatedly, we'll deal with only the last one. We can find that using the array value (the first item in the group of errors, which starts with 0) of the most recent error. Try it on your system now:

$error[0]

That brings back the most recent error text. But let's ask the error a little more about itself:

$error[0].Exception.Message

Now we're getting somewhere. We have everything we need to get started on our error function. Let's take a look at our script now, with a demo of an error. Today we'll just write the error to the screen and bail out of the program; we'll learn next time how to log the error information:

# TransferDatabase.PS1
# Backup, Compresses, Copy and Restores a Database from one Server to Another.
# Buck Woody
# Last changed: 07/02/2007
# Requires an XML file called "TransferDatabases.XML" 
#in the current directory

# Logging Process Function

# Error Handing Function
# In this case we're just writing the error info out
# to the screen. Later we'll log it so we can get the
# error information even though no one is at the console.
function ErrorHandler {
	write-host "Error Category:" $error[0].CategoryInfo.Category
	write-host "Object being worked on:" $error[0].TargetObject
	write-host "Error Message:" $error[0].Exception.Message
	write-host "Error Message:" $error[0].FullyQualifiedErrorId
}

# Do some work
Trap {
# Go to the error handler
ErrorHandler
# Stop the program.
break;
}
	
# We need to cause an error. Let's make up a fake command:
RockMeAmadeus!

There's a lot more to do, but we have the beginning of our transfer script. In the next installment we'll create the logging function, so we'll know if any errors are encountered with our process along the way.

InformIT Articles and Sample Chapters

I've only scratched the surface of error handling here. You can read more about it in this free chapter on Java here at InformIT. Although it's for another language, the concepts hold.

Online Resources

There's a free chapter you can read that describes error handling in far more detail here.