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.