Scripting Database Objects with Advanced Scripting Options in PowerShell

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.