Thursday, 29 March 2012

In the last article, Scripting Database Objects in PowerShell, I showed you the
basics of scripting your database objects so you could schedule it to run
anytime you'd like, and run it against multiple servers. In this article, I’m
going to show you how you can set different scripting options by using SMO to
pass a parameter to the overload of the script method.

Typically when you script database objects in SSMS you use the scripting
wizard, which provides you with different options for your scripts. Here’s a
screenshot of the options window from the SQL Server 2008 R2 scripting
wizard:

Scripting objects in PowerShell would be pretty useless if you couldn’t get
the same detailed level of control over your scripts as you can through the
wizard though. And this is where SMO is going to come into play. In
powershell, we’re going to start by loading the SMO provider like this:
>[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)

If you loaded the SMO assembly correctly, you’ll get a confirmation that
looks like this:

Next, you need to create a new variable and give it the ScriptingOptions
type. To do this you need to create a new object of that type like this:
>$so = new-object Microsoft.SqlServer.Management.Smo.ScriptingOptions
Now that you’ve got the assembly loaded and a variable created as a
ScriptingOptions type, you can begin to use your new variable and set its
properties. We’ll start by simply inspecting the properties. You can do this
by simply typing the name of the variable to print its contents to the
screen.
>$so
The results will look like the screenshot below, and you’ll notice the same
options from the screenshot of the wizard above.

And of course there are many more options than are in this screenshot.
Anyway though, the options you see above are only the defaults. You can change
them at will, and that’s what we’re going to do. For our purposes we’re going
to change 3 of the properties like this:
>$so.IncludeDatabaseContext = 1
>$so.ScriptDrops = 1
>$so = NoFilegroup = 1
Those options are pretty easy to understand, so I won't go into detail here
about them. And now that we’ve got the options set that we want, we can use
them in our scripting command from the last article like this:
>dir | %{$_.Script($so) | out-file c:\Tables.txt -append}
Notice that all I did was put the ‘$so’ inside the () for the script method
call. For that portion of it, there’s really nothing more to it than that. The
process isn’t perfect though. SMO itself doesn’t handle the commands perfectly,
so some properties don’t seem to have any effect on the outcome of your script.
A good example is the ‘ScriptBatchTerminator’ property. Setting this property
to true is supposed to script a ‘GO’ statement between each of the objects, but
it doesn’t. In fact, it doesn’t do anything that I can see. So what you end up
doing is writing the GO statement yourself. Remember the script for the
StoredProcedures from the last article? Well it actually won’t run. See, a
create statement has to be the first statement in a batch which means that
without the GO statement, everything after the first object will cause an
error. This is an easy problem to solve. Now, we shouldn’t have to do this,
but it is what it is, right? Here’s how you fix this issue:
>dir | %{$_.Script() | out-file c:\Tables.txt –append; “GO” | out-file
c:\Tables.txt -append}
Did you see what I did there? After the first ‘-append’ I put a semi-colon,
which terminated the line. It allows me to put multiple commands on the same
line. We use this same trick in T-SQL all the time. Then I hardcoded the text
“GO” and piped it to the same Tables.txt file with another ‘-append’ flag. So
all I’m doing is after each script, I’m printing a GO statement on its own
line. This is what SMO was supposed to do for us, but didn’t. So you have to
be careful sometimes and test that the SMO provider is doing what you expect it
to do. Most of the time it’ll serve you well though.

Other than scheduling, one of the biggest advantages you get out of scripting
your objects in powershell is the ability to control the order in which they’re
scripted. Suppose you have objects that rely not only on objects in the current
database, but perhaps they rely on objects in another database as well. You can
script those objects in the order they’re needed so none of your scripts fail.
Until now we’ve talked about scripting your objects for recovery, but this is
very useful when you’re trying to create a schema-only copy of your database for
a dev or test server. And truth be told, I really don’t like scripting all of
my objects into a single script anyway. If I’m scripting an entire DB to put
onto another server I might consider it, but for my normal recovery-level
scripting I prefer to put everything into its own file.