I have been a proponent of file-based database development for years, and am always amazed
at how many DBA's and database developers never create files from which to deploy
projects. I am also a fan of managing these files in a source management system such as
As such, Microsoft's Visual Studio for Database Professionals [the "Data Dude"]
caught my eye -- I like the manner in which it produces the files and the directory tree it
creates and stores them. This makes good sense to me. Then the February issue of SQL
Server Magazine came out with an article entitled "SQL Server 2005 Schema
Scripting," and it got my attention, again. I have developed tools in the past to script
out SQL Server 2000/2005 using both SQL-DMO and SQL-SMO. But this article,
combined with the ideas of "The Data Dude," got me going again.
So I built a widget which scripts out SQL Server 2000/2005 database objects to individual
files in a manner which mimics Microsoft's Visual Studio for Database Professionals.
This tool might be thought of as "a poor man's alternative" to the Data Dude. It
is freely available on the Internet at
. It has been out there since February 4, 2007, and people have found it somehow and
over 200 of them have downloaded it -- about 75 in the first 2 days after SSWUG.org
made a reference to it in their February 14 newsletter. This tells me that others see a need
for such a tool. Scripting the files out the way the Data Dude does makes good sense to me --
and doing it for $0.00 makes even more sense.
The program is a simple C# program which uses SQL-SMO to script out each database object
to its own file. It steps through each object type [table, view, procedure, etc.] and
creates the appropriate folders as it goes. The folder structure is exactly the same as
that produced by the Data Dude let's just adopt Microsoft's tree and file
naming convention as a standard and not try to invent a new one.
The program is a console application [.exe] which requires 3 parameters:
- The Server name
- The Database name
- The Root Path to store the files
the Server name and Database name will be created as sub-folders under this path
Optionally, for users who cannot logon via Windows Authentication, the Username and
Password may be supplied as the 4th and 5th parameters.
The entire source code is available on the SourceForge web site, but here are a couple of
highlights. After validating the parameters, it creates an SMO scripter object which contains
the scripting options. Most object types use the same settings, so these are established up
front see "Common Scripter.txt." It then simply steps through each object type
and iterates through the SMO collection of that type and sets additional, appropriate
scripting parameters including the filename. For each object it then calls the
simple "ScriptIt" method which writes out the file. See "ScriptIt.txt". An
example of how it iterates through each object type is shown for Stored Procedures
Files, source management, project deployment
Scripting out a database in the manner proscribed by the Data Dude creates dozens,
even hundreds of files for each database. What do you do with all these files? At a bare
minimum you would produce these files periodically just to make sure you have your databases
However, a better plan is to put them in a source management system such as Visual SourceSafe.
Unless you use source management, "the truth" resides only in the production
databases -- and what "the truth" was in days gone by is unknown -- except in backup
files. What an object looked like last week is not tracked. But for every type of development
other than database development, "the truth" has always resided in the source
management system. Source management tracks the who/when/why each object was changed, and
provides a means of labeling sets of files into a project [i.e., a "release"].
Plan A, therefore, is to take these files and check them into the source management system.
From that point forward, development follows the widely accepted checkout/modify/check-in
paradigm. Deployment of a project to the test database and eventually to the production
database must be done using the versions of the files modified for the project
obtained from the source management system, typically using a "get
So, for example, a CREATE PROCEDURE file originally produced by the tool and now residing
in source management is checked out. The predicate is changed to ALTER PROCEDURE and the
modifications are made and tested. The file is then checked back in to source management --
and the who/when/why are recorded. Some object types don't have an ALTER predicate,
so for these the file is modified to have both the DROP and the CREATE statement. This
tried-an-true source control methodology has the additional benefit of allowing only one
developer at a time to work on each object -- because the source management system
won't let the second developer check the file out until the first developer checks it
back in [usually after the release is deployed].
Tables, of course, contain data so dropping and recreating a table is inconvenient at best.
For tables I suggest that each table can have up to 4 supplemental files, each with a
specific name. To review, each table was given its own file containing the CREATE TABLE
statement with a naming pattern of:
Additions or changes to the table's indexes, triggers and constraints are made by
modifying the separate, individual files containing those child objects. But you need to control
other actions on tables [e.g., for lookup tables the project may need to insert, update
or delete values]. Of course, you often want to add a column or two to the table. And, again,
you need to be able to prevent multiple developers from modifying the table concurrently. So
adopt a standard such as the following for naming a table's supplemental files:
<schema>.<name>.table.alter.sql <schema>.<name>.table.insert.sql <schema>.<name>.table.update.sql <schema>.<name>.table.delete.sql
Once a table is modified and the <schema>.<name>.table.alter.sql
file is created, this same file would be reused for each ensuing alteration. All changes for
a given deployment for an individual table would be in this file in an ALTER TABLE statement.
Thus, these changes get tracked from deployment to deployment, and the current table's
structure can be recreated from scratch by redeploying the original CREATE TABLE file and
all deployed versions of the ALTER file.
The project includes a command file which automates deployment of the files in a
release. Called "Deploy.cmd", it iterates through the file tree in the
appropriate sequence and executes each file via osql.exe. It takes as arguments:
- the path of the files [where you put them when you did
the "get latest" for the current release]
- the target server
- the target database
- optionally, a flag which indicates that you want the files
deleted after being processed
That's all there is to it. Script out your databases and begin using source management
to control your database development.
The SourceForge web site [
] has a convenient way to report bugs, ask questions, submit suggestions, etc., and all are
welcome. The enhancement which allows a user to connect via Username and Password came from
just such a request entered in the project's Open Discussion Forum.
You can even register to "monitor" the project -- it you do this you will
be notified by email whenever an update to the project occurs. [The project admins do not
know who is on the list -- just that one or more people have registered.]
Of course, you will have the source code so if you want to make your own changes you are
free to do so.