Using the VSDBCMD Utility to Generate .dbschema Files

One of the utilities that ships with the October GDR (Actually it shipped with the release before this one, but I haven’t had time to write about it until now) of Visual Studio Team System for Database Professionals is the VSDBCMD.exe commandline utility. This utility does many things, but one of the most useful things to me is it’s ability to generate .dbschema files that can be used for schema comparisons.

First, a little background; I often have a need to upgrade an application database to the latest schema version (this tends to happen frequently when you practice agile database development) without destroying the data contained within the database. This is a task made simple by VSTS DBPro and the schema compare utility. The only problem though is that I am not always in a position to connect DBPro to the source schema (think Customer environment or untrusted domain).

Now, with the inclusion of the VSDBCMD utility, I can copy the required executables to any server I have access to, and run the utility to generate a schema file, which I can then use to run a schema comparison (using the full-blown tools) and then generate a delta (upgrade) script. Then I can copy that script back to the server and use the utility to deploy the update. Pretty cool!

Creating the “Lightweight” deployment source

In order to use the VSDBCMD utility in the manner I describe, you must first copy the required files to a folder on the server where you want to run it. The following files will be required:

(Located in Program Files (or Program Files (x86))\Microsoft Visual Studio 9.0\VSTSDB\Deploy (Note that the documentation on this is somewhat unclear)

VSDBCMD.EXE

DatabaseSchemaProviders.Extensions.xml

Microsoft.Data.Schema.dll

Microsoft.Data.Schema.ScriptDom.dll

Microsoft.Data.Schema.ScriptDom.Sql.dll

Microsoft.Data.Schema.Sql.dll

Microsoft.SqlServer.BatchParser.dll

Located in Program Files (or Program Files (x86))\Microsoft SQL Server Compact Edition\v3.5

Sqlceer35en.dll

Sqlceme35.dll

Sqlceqp35.dll

Sqlcese35.dll

Located in Program Files (or Program Files (x86))\Microsoft SQL Server Compact Edition\v3.5\Desktop

• System.Data.SqlServerCe.dll

Once the files are copied, your folder should look like this:

Using the Utility to Create a .dbschema File

The syntax of the command is relatively straightforward, once you get used to the fact that creating a dbschema file is known as “importing” a schema. The syntax is as follows:

VSDBCMD /a:Import /cs:<connection string> /dsp:Sql /p:ModelType:Database /model:<DatabaseName>.dbschema

For example, the database I want to import is named “CI_ANALYTICS_DW”, so the command would be:

VSDBCMD /a:Import /cs:”Data Source=xx;Initial Catalog=CI_ANALYTICS_DW;Integrated Security=SSPI;” /dsp:Sql /p:ModelType:Database /model:CI_ANALYTICS_DW.dbschema

Once the command is run, you will have a nice new and shiny .dbschema file that can be used for schema compares or many other things..