Thursday, October 2, 2008

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:

    image

    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..

    image

    3 comments:

    Anonymous said...

    This is very cool except that I cannot generate a update script from the schema compare tool when comparing with a .dbschema file, instead of a database. ANy idea?

    bong said...

    Anonymous: YES, you can compare to .dbschema file using the Schema Comparison Tool.

    When you open the Schema Comparison window from Visual Studio there are 3 options: Project, Database, and Database schema file (.dbschema).

    Have you got GDR R2 installed?

    Anonymous said...

    Very useful thanks, note theres s typo in this bit:

    /p:ModelType

    which once changed to

    /:ModelType

    obviously works fine

    Cheers!