If you’re a DBA or a database developer, you’ve probably found that point’n’click script-generation via SSMS becomes an increasingly tedious chore, and one that always increases the chance of error. Now, if you’re happy with the tedium of using SSMS to create your database scripts regularly, then use it, because it works, and it is probably the best way of getting one-off ad-hoc scripts for special purposes.

In my case, however, I want automated scripting, because it gives me the opportunity of getting the result exactly as I want it. I’m then more confident of having an up-to-date database build-script to hand, and I’m more likely to use this technique to perform tedious routine jobs such as integration, source control and creating test databases.

A live database is created from a number of SQL scripts, both DDL and DML. Script-generation is the act of reverse-engineering that live database, or a database component such as a schema or set of routines, into one or more scripts that are capable of creating an exact replica.

You’ll probably want a complete build script, as it is difficult to create a build script for a working database from individual object scripts alone, and impossible unless you are aware of the server settings and have scripted the database settings too. It is therefore wise to regularly save a complete script for building the entire database, and maybe a separate script for the database settings as well. You’ll probably also want to use script-generation to get the source of individual routines, tables and views for source control, for editing and for your own records.

There are a number of choices to be made when creating your database scripts, depending on the problem you’re trying to solve. It’s like buying a frou-frou cup of coffee. You are bombarded with decisions. Are you updating the schema of an existing database, or are you creating it from scratch? Do you only wish to update the routines? Do you want to temporarily disable the constraints in order to import data in the ‘wrong’ order? Do you want to leave out the DRI until you’ve imported all the data?

You need this flexibility required in a script-generation solution because you have a fair amount of complexity to deal with. A database application, at any point in time, consists of one or more databases on one or more servers, and may have more than one version or fork being developed. They may be using different data. As well as the code for all the routines, views and tables, there will be database settings and server settings. Finally, there will be data, even if just the basic enumerations and static data without which nothing works. As well as the code in the shared ‘public’ database, you may also have stealth things you are trying out, and sandbox stuff that needs to be preserved. You will also need to script your endpoint configuration and tasks that will go on the SQL Server agent. If you’re doing serious website work, you’ll have queues managed by service broker too.

If the production-DBAs have scripts for all of this, for all current versions, along with migration and rollback scripts, then they are smiling.

As well as squirreling away the code in order to preserve the work of the team, keep track of progress, maintain test-cells, do builds and rollbacks from builds, and to relate bugs to code alterations, you need codes to understand the database. You can understand a minnow of a database such as Adventureworks through Point n’ click in SSMS, but for an industrial-strength behemoth, then it is far quicker to eyeball down the build scripts. I know of very few ways to generate database scripts, and a lot of these do it wrong, because the scripts are formatted as a machine-to-machine communication. Table build scripts, for example, can be written for legibility or merely in order to get a correct build. In order to quickly learn a database, you need the legible version.

No, I had to write my own version and it has paid dividends. Functions, for example, are easier to understand with a structured comment block listing the parameters and comments in extended properties, and even where they are referenced and what they reference. Tables are far better with the simpler constraints written in-line, and comments on both table and column pulled from the extended properties.

Automated scripting of database DDL

Let’s start with the obvious technique. We’ll use PowerShell and Server Management Objects (SMO). No, don’t panic. SMO is a monster, but that’s because it is written for many purposes, the worst of which is providing an interface between SSMS and the servers. In most cases, there is already a high-level interface for the likes of you and me. I’ll admit that SMO isn’t easy. It is always a bad sign when you’ve got a problem with a script, and you reach for Google, only to find nothing more than a string of Chinese characters on half a page, and a similar StackOverflow question left unanswered since 2008, save for advice to use SSIS instead.

This sort of thing tends to happen when you’re using SMO, which is such a shamebecause it is so powerful. In fact, almost any job that you can do via SSMS you can do through SMO. With PowerShell and SMO, you can work magic, but with that strange, lonesome feeling that not many people have ever walked down the same path. It’s the awful documentation, combined with the intricate, arcane, multi-layered interface that makes SMO hard work for the uninitiated, but it shouldn’t be like this.

Microsoft has always shown its ambivalence in letting users loose on SMO, by neglecting to provide anything remotely resembling adequate documentation. All we get is minimal, and probably machine-generated, documentation of the SMO classes, methods and so on. Even the examples have errors. Microsoft has to keep SMO up to date because it is used for SSMS, but there seems to be a great deal of passive resistance to supporting users who need to use it for scripting. For this reason, I’ll be keeping these examples as simple as I can.

Getting stuck in quickly

There is usually a quick way to do things. Here is the PowerShell to script out the MyDatabase database from MyServer into the local directory E:\MyScriptsDirectory’ (it will do others, of course, by changing the assignments to the three variables at the head of the script). Note that in these PowerShell scripts I’ve opted to avoid the terse style, mainly because the terse style is less intelligible for those of us who just want to use PowerShell without getting too absorbed.

I reckon this is the simplest PowerShell script to get an executable build script, and it isn’t too painful. Die-hard SMO-heads will notice that I have to write to a file via SMO in order to get the batch-terminator GO into the script. To do this, I’ve created a ‘ScriptOptions’ object, which isn’t entirely necessary yet, but will be once we increase the complexity of the task. If you run the script, it will successfully build a database, but there will be a lot missing, because we’ve been using the default options for generating the script. You’ll have no DRI. The tables, in other words, won’t have their constraints and indexes, or any dependent objects at all. It will miss out all the extended properties as well.

There is some work to be done. Not all the defaults for the script options are sensible. A quick bit of PowerShell to query the ScriptOptions object will tell us what the defaults are. I’ll print them all out because this is a useful reference when you’re struggling with a script task. This is the equivalent of the options for the frou-frou cup of coffee, and you’ve just been served ‘black without sugar’. Our scripting options are below, along with the defaults.

Options: ‘Do you want cinnamon with that coffee, sir?’

These are the scripting options with their default settings. I haven’t explained them, because they are either obvious or  undocumented

FileName
Encoding	System.Text.UnicodeEncoding
DriWithNoCheck	False
IncludeFullTextCatalogRootPath	False
BatchSize	1
ScriptDrops	False
TargetServerVersion	Version110
TargetDatabaseEngineType	Standalone
AnsiFile	False
AppendToFile	False
ToFileOnly	False
SchemaQualify	True
IncludeHeaders	False
IncludeIfNotExists	False
WithDependencies	False
DriPrimaryKey	False
DriForeignKeys	False
DriUniqueKeys	False
DriClustered	False
DriNonClustered	False
DriChecks	False
DriDefaults	False
Triggers	False
Statistics	False
ClusteredIndexes	False
NonClusteredIndexes	False
NoAssemblies	False
PrimaryObject	True
Default	True
XmlIndexes	False
FullTextCatalogs	False
FullTextIndexes	False
FullTextStopLists	False
Indexes	False
DriIndexes	False
DriAllKeys	False
DriAllConstraints	False
DriAll	False
Bindings	False
NoFileGroup	False
NoFileStream	False
NoFileStreamColumn	False
NoCollation	False
ContinueScriptingOnError	False
IncludeDatabaseRoleMemberships	False
Permissions	False
AllowSystemObjects	True
NoIdentities	False
ConvertUserDefinedDataTypesToBaseType	False
TimestampToBinary	False
AnsiPadding	False
ExtendedProperties	False
DdlHeaderOnly	False
DdlBodyOnly	False
NoViewColumns	False
SchemaQualifyForeignKeysReferences	False
AgentAlertJob	False
AgentJobId	True
AgentNotify	False
LoginSid	False
NoCommandTerminator	False
NoIndexPartitioningSchemes	False
NoTablePartitioningSchemes	False
IncludeDatabaseContext	False
NoXmlNamespaces	False
DriIncludeSystemNames	False
OptimizerData	False
NoExecuteAs	False
EnforceScriptingOptions	False
NoMailProfileAccounts	False
NoMailProfilePrincipals	False
NoVardecimal	True
ChangeTracking	False
ScriptDataCompression	True
ScriptSchema	True
ScriptData	False
ScriptBatchTerminator	False
ScriptOwner	False

This is a scarily complicated set of options. One can’t help wondering why some of these options would be required. However, we can soon put our script right without too much bother. Your own requirements may be different, but I was aiming for an exact copy of AdventureWorks in my testing!

Making an exact copy of AdventureWorks

If you test a database built with this script against the original, it gives a pretty good account of itself. All that’s missing are some extended properties on indexes, but there is no switch that one can flip to tickle those out of SMO, so I suspect that someone has made a mistake.

We did this intermediate version because it is simple and demonstrates a clean technique which you can take and expand on. It’s great for archiving a complete build script that you can use in source control alongside the individual object scripts. However, I’m going to include a more complete version that will give you a database build script and an object-deletion script as well as the object-build script, all concatenated into one script. You’ll begin to understand why I like to create a ‘ScriptingOptions’ object to store the options, since it is more efficient for this sort of job.

Here is a version that  allows you to specify databases by wildcard, so you can do just one or as many as you like. Here, I’ve added the ability to use SQL Server credentials or Windows authentication, and I use the later version of SMO called, confusingly, sqlserver. I also show an alternative way of loading the script options that would enable you to get them from a file if necessary.

Getting database settings and object drops into a database-script

This isn’t entirely what we want for other purposes, of course. What about when you want to create a database without indexes, constraints or triggers, import the data in BCP fast-mode and then add the indexes, constraints and triggers? Yes, you can squirrel away far more test-runs this way, and load them rapidly, but in order to do it, you need a build script without them first, and a second build script with them only. With the first ones, you can have a ‘knock-down’ kill script that deletes everything from the database before you start, but you definitely don’t want it for the second script. You’ll soon be eyeing up all those scripting options, though, believe me. I’ll be covering a lot about this in future articles.

Automated scripting of objects

A second task is to save each object to a separate file. You’ll need to do this to get your local (unshared) database into source control if you’re not using SQL Source Control. The simplest way of doing this, if you are lucky enough to have SQL Compare, is

You can do it in PowerShell, and you will, again, have more knobs you can twiddle to get the individual scripts exactly how you like them.

Here is a simplified script that shows you one of the several methods of doing this. Like SQL Compare, it saves each object type into its own subdirectory.

This time, we get SMO’s EnumObjects method for the current database object, then get the scripter object, via the Script method, to script out each object individually and save it to a separate file. Each filename is generated from the name of the object, and the directory name is generated from its object type. You can, of course, be very selective about what you script out and you’ll see that scripting out a single object type, such as a table, is very simple.

In this following script, we’ll save just the tables, scripting different schemas into different directories and adding the DRI, indexes, extended properties and triggers to each table. It would be equally simple to script whatever types of objects you want just by ‘or’ing the DatabaseObjecTypes to taste. ( in the previous script, I specified ‘all but…’)

Automated scripting of static data

The last task we’ll set ourselves is to script out static data. This will really just include all those small tables without which your database simply won’t work. Now, there is no way that anyone but you will know which tables this includes, but don’t ‘go ape’ with this script, since it is scripting INSERT statements and that sort of thing doesn’t scale effectively for big tables. No, sir: you’ll want native-mode, fast-mode BCP for that.

This time, I’ve used a slightly different approach, in that I’ve actually constructed the URNs from the (maybe qualified)  names of the table; this means the schema too if you specify it, and also the database if you want that too. You just specify what tables you want to script and we just go and do it. With SMO there are always several ways of getting to your destination.

Now we’re making progress, and I’m hoping that, in these simplified scripts, I’ve given you some useful clues as to how to generate particular types of build scripts. I’ll let you turn them into robust command-line tools with parameters and all the error handling, logging and other bits and pieces you’ll want. In the next article, I’ll explain how you can extend this functionality by actually executing SQL batches via SMO. This will allow you to automate migration scripts and do integration testing and all manner of other things all from the one script.