If you haven’t got a database versioning tool or are not using it (for whatever reason), one of the easy means of self-preservation for DBAs as well as developers is to script your databases every once in a while. This is mainly to avoid having to restore the full database to be able to view an earlier state.

Such a job can easily be automated using Powershell. There are various versions of such scripts available on the net, but the version we are using is available using the link below.

As you may know, you will need to provide scripting options information to get the full details and the complete list of e.g. indexes, statistics, grants, … in your database. The options we are using are listed here:

$CreationScriptOptions = new-object ("Microsoft.SqlServer.Management.Smo.ScriptingOptions")
$CreationScriptOptions.ContinueScriptingOnError = $true
$CreationScriptOptions.ScriptOwner = $true
$CreationScriptOptions.ToFileOnly = $true
$CreationScriptOptions.AppendToFile = $true
$CreationScriptOptions.AnsiPadding = $true
$CreationScriptOptions.AppendToFile = $true
$CreationScriptOptions.Bindings = $true
$CreationScriptOptions.ChangeTracking = $true
$CreationScriptOptions.DriAll = $true
$CreationScriptOptions.EnforceScriptingOptions = $true
$CreationScriptOptions.ExtendedProperties = $true
$CreationScriptOptions.FullTextCatalogs = $true
$CreationScriptOptions.FullTextIndexes = $true
$CreationScriptOptions.FullTextStopLists = $true
$CreationScriptOptions.IncludeDatabaseRoleMemberships = $true
$CreationScriptOptions.IncludeFullTextCatalogRootPath = $true
$CreationScriptOptions.IncludeHeaders = $true
$CreationScriptOptions.Indexes = $true
$CreationScriptOptions.LoginSid = $true
$CreationScriptOptions.Permissions = $true
$CreationScriptOptions.SchemaQualify = $true
$CreationScriptOptions.SchemaQualifyForeignKeysReferences = $true
$CreationScriptOptions.ScriptBatchTerminator = $true
$CreationScriptOptions.ScriptDataCompression = $true
$CreationScriptOptions.ScriptOwner = $true
$CreationScriptOptions.ScriptSchema = $true
$CreationScriptOptions.Statistics = $true
$CreationScriptOptions.Triggers = $true
$CreationScriptOptions.XmlIndexes = $true

Recently we noticed some of the database scripts generated were not of the expected size, so we started investigating what went wrong. While debugging the script using the Powershell_ISE, we discovered an invalid view definition ( a view referring to a table that no longer exists ) caused the script transfer to fail.

First thing we double checked was the use of the scripting option that should prevent failure in case of errors:

$CreationScriptOptions.ContinueScriptingOnError = $true

As shown, this setting was in use and had been set to $true, So I would not expect this scripting process to fail at all.

The second step was to start playing with the scripting options to figure out which setting caused the failure. The wild goose chase started with disabling the ExtendedProperties scripting option, because the proprietary database didn’t use extended properties at all.

The next test run failed for the same reason as before, so the setting was re-enabled and the chase went on.

Then I noticed the option EnforceScriptingOptions, which didn’t make much sense to me, because we specified all needed options individually. Bing guided me to msdn article “ScriptingOptions.EnforceScriptingOptions Property” http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.enforcescriptingoptions.aspx

This page states:

This demystification really didn’t ring a bell at all. Having read this single line a couple of times, trying to avoid getting lost in translations, finally I interpreted this switch as “Use this to activate all options“.

Guess what: I don’t want ALL options, I only want it to use the options I selected!

Disabling the option the next test run completed successfully. Double checking the scripted result, I found everything I expected to find in the script.

Scripting a more complicated database, that uses more of the available sql server means, ( that didn’t fail having the EnforceScriptingOptions set to true ) and comparing the result to a previously scripted file, showed no meaningful differences ( except for scripting timestamp and target file information ).

Conclusion,

Due to sheer luck our little scripting tool worked well over the years, or at least we didn’t have the need for a script of a database that – temporarily – suffered this situation where the scripting tool failed. This proved once more to never be off guard and always to sample your failsafe tools and results.

I would certainly like to hear your findings working with this SMO scripting option with regards to which options it is supposed to actually set to true and which options would cause an invalid view definition to fail this way of scripting a database.

Happy scripting,

Johan