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:

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.

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

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

After the schema comparison, we get the following result:

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:
   
• 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:
  

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.