Pages

Thursday, 29 March 2012

Scripting Database Objects in PowerShell

Scripting Database Objects in PowerShell

One of the most important tasks a DBA can perform is to make sure he can
recover from unexpected disasters. And while backup and restore is usually the
method chosen for recovery, scripting the database schema can be a very powerful
tool. Quite often a user will drop a stored procedure, or change a view by
mistake and you don’t want to restore the entire database just to get back a
single piece of code.

The problem is that scripting database objects for a single server is a
manual process for the most part and keeping up with schema changes for a single
server can be hard enough, but for an entire enterprise it’s nearly
impossible. There are some third party tools that can help, but most of them
don’t have command lines that are rich enough to handle such a process. This is
where powershell comes in very nicely. With powershell you can easily script
all the objects in your database manually, or schedule the scripting anytime
you'd like. And you can do it for as many of your servers as you'd like.
Let’s start by scripting all the tables in a database. For our purposes
we’re going to connect to a database and navigate to the ‘tables’ node.
>cd SQL:\SQLServer\localhost\default\databases\MyDB\Tables
Now that we’re connected to the ‘tables’ node we can script all of the
objects.
Dir | %{$_.Script()}

It’s just that easy. Now I’ll go ahead and
explain the code. First you start by pulling a list of tables with the ‘dir’
command (you can also use ‘gci’). Then you pipe ‘|’ the results of that command
to the foreach loop command. Here I’m using the alias for the foreach command,
which is ‘%’. Everything that goes inside the foreach (%) loop is surrounded
by the curly brackets {}. Once inside the foreach construct, you call the
script method for each object encountered. The ‘$_’ is the built-in iteration
variable in powershell. So for each iteration of the foreach loop the ‘$_’
represents the one currently in the loop. So what you’re saying here is foreach
object, call its script method. The script method is overloaded and can take a
parameter but we won’t discuss that until the next article.
Now that those basics are out of the way, let’s see how we’d script out the
stored procedures in the same database.
>cd SQL:\SQLServer\localhost\default\databases\MyDB\StoredProcedures
>Dir | %{$_.Script()}
Yep, that’s right. The method you use to script the objects doesn’t change.
Only the node you’re in changes. And while you could do some fancy tricks to
change nodes, the easiest way to script all the objects in your database is to
just repeat the above steps for each node you want to script. It would look
something like this is practice:
>cd SQL:\SQLServer\localhost\default\databases\MyDB\Tables
>Dir | %{$_.Script()}
>cd SQL:\SQLServer\localhost\default\databases\MyDB\StoredProcedures
>Dir | %{$_.Script()}
>cd SQL:\SQLServer\localhost\default\databases\MyDB\Views
>Dir | %{$_.Script()}
Of course, all this does is print the output to the screen and that’s only
going to be minimally useful at best. What you really want to do is save the
scripts to a file so you can actually restore them if you need to. So we’re
going to modify the script just a little so we can save it to a file.
>dir | %{$_.Script() | out-file c:\Tables.txt -append}
What this is saying is that once you have the object scripted, pipe the
output to a file located at c:\Tables.txt and append the output to the end of
the file. If you don’t use the ‘-append’ flag the file will be overwritten for
each object and you’ll only get the last object in the file.
Now, let’s suppose that you wanted to script only those objects in a certain
schema. For our purposes let’s say that you only want objects in the HR
schema. All you have to do is pass the the ‘Dir’ command to a where-object to
limit the results before scripting them. It would look like this:
>dir | %{$_.Script() | where-object{$_.Schema –eq “HR”} | out-file
c:\HRTables.txt -append}

Turning this into a process that runs on multiple boxes is relatively
simple. There are a number of ways you could do this, but for our purposes I’ll
choose the easiest to follow. We’re going to be putting our list of servers
into a text file and cycling through them one at a time. So create a new text
file and put your servers inside. Just put one on each line like so:
Server1
Server2
Server3
Now all you have to do is put create a foreach that will cycle through each
server in the file. Here’s the entire line of code.
>get-content c:\Servers.txt | %{cd
SQL:\SQLServer\$_\default\databases\MyDB\Tables; dir | %{$_.Script() | out-file
“c:\$_Tables.txt” -append}}
It’s just that easy. The only changes I made to the original code were to
replace the server name with the ‘$_’ variable that represents the current
server in the loop coming from the Servers.txt file, and enclosing the out-file
filename in double quotes so I can prefix the filename itself with the ‘$_’
variable so that the server name is depicted in the filename itself. Also, the
backtick (`) is necessary because it’s an escape character. It tells powershell
that the ‘$_’ is the variable name and not ‘$_Tables’.
Unfortunately, as it stands, the code will fail if you just paste it into
SSMS to run. I'll explain why and solve the problem in the next article: Scripting database objects with advanced scripting options in
Powershell.