VSDBCMD.exe part 2

Recently, I blogged about the new VSTS4DBP 2008 GDR command line utility. In this post, I will dig into the "how to use" the utility.

Prerequisites

To be able to use the utility, we need to copy some files to a folder:
C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Deploy:
– VSDBCMD.exe
– DatabaseSchemaProviders.Extension.xml
– Microsoft.Data.Schema.dll
– Microsoft.Data.Schema.ScriptDom.dll
– Microsoft.Data.Schema.ScriptDom.Sql.dll
– Microsoft.Data.Sql.dll
– Microsoft.SqlServer.BatchParser.dll

C:\Program Files\Microsoft SqlServer Compact Edition\V3.5:
– Sqlceer35en.dll
– Sqlcecme35.dll
– Sqlceqp35.dll
– Sqlcecs35.dll 

C:\Program Files\Microsoft Sql Server Compact Edition\V3.5\Desktop:
– System.Data.SqlServerCe.dll

These files can be copied on the target machine or a thumb drive. They must be in the same folder in order to properly use VSDBCMD. In my directory, I created a folder “ImportedSchemas”. It will contain all my .dbschema files:image

Reverse engineer an existing database

Before we reverse engineer our target database, we will make some changes to its structure. Let's rename the FirstName column to First_Name in Staging.Stg_Customers table.

image

The VSDBCMD term for database reverse engineer is import. So, the option we use at command line is /a:Import.
image

This command generate a file called ImportedSchemas\MyTarget.dbschma. This the offline representation of the target database on the machine where the VSDBCMD utility has been executed:image
Now, using this file, we are able to compare it with our database project. We simply call the schema compare tool from the data menu and set the screen as this:
image

After the schema comparison, we get the following result:
image

The comparison tells that the target database Stg_Customers differ by one column; exactly the one we changed before generating our MyTarget.dbschema file!

Deploying our database project

Database project can be deployed using VSDBCMD by using two similar command line option.

  • Generate a script that can be reviewed before executing it against the target database. This gives us a .SQL file that can be specidically named using the /DeploymentScriptFile option. To generate the script we use the /deploy option:
    image 
  • Generate a script and directly deploy the target database. This gives us a script that can be version controlled and immediatly deployed to the database. To do it, we use the /deploy and /dd option:
    image

Summary

This article demonstrated how the new VSDBCMD utility can be used to import or deploy a database. It is very useful when you want to compare a database against your database (DBPro) project or deploy it without using Visual Studio.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: