SQL Server Reference Guide

We're in the final tutorial on my series on scripting with PowerShell. So far I've explained how to get and install PowerShell, and I explained the basic concepts you need to work with it. I also introduced you to error handling and a logging process, and in the last tutorial I showed you how to work with a control file to make the process truly extensible. Before we press to the final part of the script, I need to explain something about the particular method we're using here.

We're using the control file to list out the servers and databases that we want to work with. We'll iterate through two loops – one for the server, and one for each database. This is very useful; because we can list every server and database we need in one place. But there are situations where this method is not the best approach. If you have hundreds of servers, or even thousands, then the each server is waiting on the one before it to complete. By the end of the chain, it could be three days later if the processes take a long time!

There are ways around this dilemma. The first method is to split the operations up into a function, and call the function multiple times for each server or database. That way the processes spin up independently. We'll cover that in another tutorial. Another method, perhaps one that is even better, is to host the independent processes on each server. Then you can call the process to start on each server from a single location. That's another tutorial as well.

For now let's assume that we have only a few servers and databases that we want to work with. For simplicity's sake, I'll back up from one server and restore to the same, but you can use this technique to adapt the process to one that works for you.

So for now, I'll take the XML file we created in the last tutorial and only embed one server and one database on it.

To refresh your memory, the script will read the control file, backup a database, compress it and then copy, decompress and restore it to another server. That sounds like a lot of work for a script – and it is, but once you write it and test it, you'll be able to perform the work over and over, quickly and easily.

In this tutorial we'll take all of the concepts I've shown you in the series, and put it all together into one single script. But before we do, I need to explain one more construct – and one of the most important for us DBAs: how to access SQL Server. It's actually pretty simple to do, but the complications come in when you find out there are multiple ways to do it. I'll show you a simple method today, step by step, that doesn't provide any return sets of data. We're only sending a command to the server, so we really aren't interested in selecting data from a table or a view. There are ways to do that, and I'll show you those in another tutorial. In this instance we're only interested in a database backup. We will get a return code back that the server ran the backup, but we won't get any more than that, which is exactly what we want. You never want the server to do more work than it should – you just want to get in, get what you want, and get out.

To begin, we need to create a few SQL Server objects to use to get to the server. Once again, there are many objects that PowerShell can use to get to SQL Server. We'll stick with just two: A SQL Server connection, and a SQL Server command. The connection gets us to the server, and the command does the work. Setting up those objects is really easy.

First, let's create a variable for the connection called $sqlConnection, and call a new object. The object is in the System space, which holds just about everything that the server knows about. Within that space is another space called Data, which knows a lot about working with data sources and data sets. And within that is yet another space called SQLClient. That is the Native Client that SQL Server uses to access servers and databases. And then within that space is an object called SqlConnection, which does exactly what it sounds like it would: it handles connections to the server. And we can do all that with a single line in a PowerShell window:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection

Following the same logic, within the SqlClient space is another object we can use, called sqlCommand. Using the same format as above, we can make another variable called $SqlCommand, which holds this object:

$sqlCommand = new-object System.Data.SqlClient.SqlCommand

If you type out one of those variables, you'll see that you can see a lot of information about them. But right now, the connection is closed, and the command is empty. Let's work with the connection first. To get to the server, we have to tell the connection object we created which server and database we want to talk to, and what our security credentials are. If you've ever set up an ODBC string, it's much the same. It's also similar to connection strings in C# and other languages. Using the $sqlConnection object we made, we'll take one of its properties called ConnectionString and enter the data needed. In this example, I have a server called SQLServerOne, and I'm connecting to the master database on that server. I'll set the "integrated authentication" to true, which means I'm using my Windows account to get into the server. Here's how that looks – change the variables I just mentioned to something that works on your system:

$sqlConnection.ConnectionString = "server=SQLServerOne;integrated security=true;database=master"

Perfect. With the connection created and the connection string ready, we can open the connection with a method, or command, on the object called Open():

$sqlConnection.Open()

With the connection opened, we need to send a command using the connection. But first, we need a command. To do that, let's use another property, this time on the $sqlCommand object we made:

$sqlCommand.CommandText="SELECT @@VERSION"

We need one more property to tie the command and the connection together. Take a look at how we do that in the following line:

$sqlCommand.Connection=$sqlConnection

We're ready to execute the command. All we have to do is call a method (command) on the $sqlCommand object, since it is tied to the connection, and the connection has the proper connection string. The particular method we'll use here is called ExecuteNonQuery(), and it runs a command but doesn't ask for any data back. All it does is return a -1 if the command works, and an error number if it doesn't. Don't worry; we'll trap the full error text in our larger program. For now, the line below is all we need to do to run the command:

$sqlCommand.ExecuteNonQuery()

All you'll get back is a -1 if the command works properly. Finally, we need to close the connection. Once again we'll call a method, this one for the $sqlConnection called Close():

$sqlConnection.Close()

And that's all there is to it – at least for this connection type. We don't need any data back; we just need to take a backup, so this process is perfect.

Now that you understand how to connect to the server and database and run a command, we'll put the final script together. The script is quite long, but it is laid out in easy-to-understand sections. Let's take a look at the structure and then I'll list the full script.

First I have a section of comments, as you've seen in the script in the previous installments. Next, we create a few functions. The first sets up the logging process, and the second takes care of the error handling. You've seen both of these in the previous tutorials. The next function is the same process we just went wrote, but as a function it takes parameters for the server, database and command variables.

In the last section of the script, you can see that we're using the XML control file from the last tutorial to iterate through the servers to perform the work. In this script, I'm backing up two databases (Test1 and Test2) on the same server (NSAXP\SQLEXPRESS) to a directory called c:\temp\Source. I then compress the files using the PKZIP program, I copy the files to another directory on the same server called c:\temp\Destination, decompress them, and then apply the restore to the same server, but with the name "Reporting" appended to them. You can alter these names to your test server environment. By the way, if you are only working with a few static servers, you can just substitute the actual names of your servers and databases for the control file process. That won't be as extensible, but it will work just fine.

The script has comments that will show you each block. You should be able to follow the steps – read through the script and use the information from this series to decode how it works:

# TransferDatabase.PS1
# Backup, Compresses, Copy and Restores a Database from one Server to Another.
# Buck Woody
# Last changed: 07/16/2007
# Requires an XML file called "TransferDatabases.XML" 
# in the c:\temp\ directory
# Get the Event Log Object, since we’ll need it throughout the script:
$log = New-Object System.Diagnostics.EventLog 
# Logging Process Function
Function LogThis($logType, $logEventID, $logEntryType, $logMessage) 
   {
   # Set the type and source
   $log.set_log($logType)
   $log.set_source($programSource)
   # Write the information to the approriate log
   $log.WriteEntry($logMessage, $logEntryType, $logEventID)
   }
# 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 
   {
   # We’ll use Application for everything here, but you can use others if you like
   $logType = "Application"
   # We’ll use a single number here, but you can use anything you like
   $logEventID = 7777
   # For errors we’ll use Error
   $logEntryType = "Error"
   # And we’ll build the message string with a carriage return at the end of each line
   $logMessage = "Error Category:" + $error[0].CategoryInfo.Category 
   $logMessage = $logMessage + "´rObject being worked on:" + $error[0].TargetObject 
   $logMessage = $logMessage + "´rError Message:" + $error[0].Exception.Message 
   $logMessage = $logMessage + "´rError Message:" + $error[0].FullyQualifiedErrorId 
   # Send the information to the Logging function
   LogThis $logType $logEventID $logEntryType $logMessage
   }
# SQL Server Access
Function sqlServerAccess($sqlServerVariable, $sqlDatabaseVariable, $sqlCommandVariable) 
   {
   $programSource = "SQL Server Access for server: " + $sqlServerVariable + "´rDatabase: " + $sqlDatabaseVariable + "´rCommand: " + $sqlCommandVariable 
   write-Host $programSource
   # Connect and run a command using SQL Native Client, No return
   $sqlConnection = new-object System.Data.SqlClient.SqlConnection
   $sqlConnection.ConnectionString = "server=" + $sqlServerVariable + ";integrated security=true;database=" + $sqlDatabaseVariable
   $sqlConnection.Open()
   $sqlCommand = new-object System.Data.SqlClient.SqlCommand
   $sqlCommand.Connection=$sqlConnection
   $sqlCommand.CommandText= $sqlCommandVariable
   $sqlCommand.ExecuteNonQuery()
   }
# Main script Block
cls
# Set the global variable of where we are in the code
$programSource = "Main Block"
Trap
   {
   # Go to the error handler
   ErrorHandler
   continue;
   }
# Begin the logging
$logType = "Application"
$logEventID = 7777
$logEntryType = "Information"
$logMessage = "Transfer Database Process Starting" 
LogThis $logType $logEventID $logEntryType $logMessage
# Create an XML document object, read in the file
$programSource = "Read XML File"
write-Host $programSource
$doc = [xml]( Get-Content c:\temp\servers.xml )
# Fist loop for the servers
foreach ($server in $doc.servers.server) 
   {
   $programSource = "Server Connection for server " + $doc.servers.server
   write-Host $programSource
   # Second loop for the databases
   foreach ($database in $server.database) 
     {
     $programSource = "Database Backup"
     write-Host $programSource
     $sqlCommandVariable="BACKUP DATABASE " + $database.databasename 
     $sqlCommandVariable=$sqlCommandVariable + " TO DISK = ’c:\temp\Source\" + $database.databasename + ".BAK’"
     sqlServerAccess $server.servername "master" $sqlCommandVariable
     # Zip the files
     $programSource = "File Compression"
     write-Host $programSource
     $zipSourceName = "c:\temp\Source\" + $database.databasename + ".BAK"
     $zipDestinationName = "c:\temp\Source\" + $database.databasename + ".ZIP"
     c:\batch\pkzip25.exe -add $zipDestinationName $zipSourceName
     # Copy the files
     $programSource = "File Copy"
     write-Host $programSource
     $copySourceName = "c:\temp\Source\" + $database.databasename + ".ZIP"
     $copyDestinationName = "c:\temp\Destination\" + $database.databasename + ".ZIP"
     copy $copySourceName $copyDestinationName
        # Unzip the files
     $programSource = "File DeCompression"
     write-Host $programSource
     $zipSourceName = "c:\temp\Destination\" + $database.databasename + ".ZIP"
     cd c:\temp\Destination
     c:\batch\pkzip25.exe -extract $zipSourceName
     $programSource = "Database Restore"
     write-Host $programSource
     $sqlCommandVariable="RESTORE DATABASE " + $database.databasename + "Reporting" 
     $sqlCommandVariable=$sqlCommandVariable + " FROM DISK = ’c:\temp\Destination\" + $database.databasename + ".BAK’ "
     $sqlCommandVariable=$sqlCommandVariable + " WITH MOVE ’" + $database.databasename + "’ "
     $sqlCommandVariable=$sqlCommandVariable + " TO ’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" + $database.databasename + "Reporting.mdf’"
     $sqlCommandVariable=$sqlCommandVariable + " , MOVE ’" + $database.databasename + "_log’ "
     $sqlCommandVariable=$sqlCommandVariable + " TO ’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\" + $database.databasename + "Reporting.ldf’, REPLACE"
     sqlServerAccess $server.servername "master" $sqlCommandVariable
     }
   $sqlConnection.Close()
   }
# End with logging
$logType = "Application"
$logEventID = 7777
$logEntryType = "Information"
$logMessage = "Transfer Database Process Completed" 
LogThis $logType $logEventID $logEntryType $logMessage

InformIT Articles and Sample Chapters

Active Data Objects (ADO) is another method you can use to access SQL Server, this time with result sets. You can brush up on your ADO.NET knowledge with a free chapter from Teach Yourself .NET in 21 days.

Online Resources

My friends over at Simple-Talk have a great series of articles on using the new Server Management Objects (SMO) with SQL Server. Check it out here.