There’s an underappreciated feature in Visual Studio 2008 Database Edition (DbPro) and that’s VsDbCmd.exe.  If you’re using DbPro to manage your database schema during development, you’re doing it with Visual Studio installed on your desktop.  When you need to deploy changes to production, you do a Database Comparison between your project and your production database and DbPro generates an update script for you that publishes your changes to the production database.

This works great if you can connect to your production database from your developer workstation.  What if you can’t connect from your developer workstation?  Do you need to install DbPro on the production machine?

Thankfully, the answer is no.  This is where VsDbCmd.exe comes in.  VsDbCmd is a command-line tool that you can put on a USB thumb drive (or copy over the network) to a machine that does not have DbPro installed.  Once you’ve copied VsDbCmd to that machine, you have two very helpful features:

1. If you want to script out a production db schema for putting under source control you can do an “Import”.  This generates a *.dbschema file that contains all the details of the schema on your database including the source for all your tables, views, stored procedures, functions, permissions, etc.  You can then take this *.dbschema file and bring it back to your developer workstation and import it into your DbPro database project.
2. You can take the output from your DbPro database project on your developer workstation and use VsDbCmd to deploy your changes to the production database.  You have the option to either generate a SQL script that you can edit and run yourself or have VsDbCmd do the deployment for you.  (BTW, VsDbPro is smart enough to know how to deploy just the updates to the schema and preserve the data in the database.  It’s not going to drop the database and re-create from nothing unless you ask it to.)

But there’s a catch.  You’ve got to package up a handful of files that VsDbCmd needs to run.  This step is a little confusing and tedious. Once you get everything packaged up and copied over to your database machine, you have to remember the command-line syntax for doing your Import or Deploy.  Not that hard but there’s some hunting around and tweeking required to get it right.

It would be nice if there were a user interface for VsDbCmd, huh?  Well, I wrote one!  (You’re welcome. )

Step #1: Download the tool

Click here and click “Install” to download the latest version to your development machine.

Step #2: Run it.

If it didn’t start after you did the install, you should have an entry in your Start menu called “VsDbCmd Utility”.

Step #3: Create a “standalone” package

Click the “Create VsDbCmd Package” button.

Enter the directory on your local disk where you want to create the standalone package.  If you’re going to do a “Deploy”, then click ”Include output from database project” and set the “Path to Sql Directory” to the location of your database project’s compiled output.

Click “Create Package” to gather all the required files and create the package directory.  Now click, “Open Package Directory in Explorer” to go to the newly created package directory.

This package directory has everything you need to run your Import or Deploy including a copy of the VsDbCmd Utility UI (Benday.DbProTool.WinUI.exe).  Copy this entire directory to your production database machine over the network or by USB thumb drive and then run Benday.DbProTool.WinUI.exe to do an Import or a Deploy.

VsDbCmd Database Schema Import

Run Benday.DbProTool.WinUI.exe from the standalone package directory and click “Import Database”.

You should now see the VsDbCmd Import Database Utility screen.  From here you can script out the settings from your Master database or script out a user database schema (e.g. Northwind, AdventureWorks, etc).  When you script out a database, it gets converted into a DbPro “Model” file (*.dbschema) file.

Choose what kind of model you want to generate – Server or Database. 
Specify the filename you want to use for your Model file. 
Type in the database connection info.

Click “Create Command” to generate the vsdbcmd command for this operation.  Once you’ve generated the command, you can click “Run Command” to run the command.

Use VsDbCmd To Deploy a Database or Deploy Changes to a Database

Run Benday.DbProTool.WinUI.exe from the standalone package directory and click “Deploy Database”.

You should now see the VsDbCmd Deploy/Update Database Utility screen.  From here you can use the output from the database project on your developer workstation (or build server) to deploy your database changes to a running database.

Under “Action”, choose either “Create SQL Script” or “Deploy To Database”.  If you choose “Create SQL Script”, specify the name of the file that you want for the update script.

Disclaimer: The best practice is to do “Create SQL Script” and review it to make sure that it is doing what you want BEFORE you actually deploy it to the database.  You should also create a backup of your database BEFORE you deploy any changes to your database using this tool or any other tool.

Under “Database Project Files”, choose the Model and Manifest files that you want to deploy.  These files are part of the compiled output from your Visual Studio Database project (DBPro project) on your developer workstation.  If you chose the “Include output from database project” option when you created the standalone package, then these files should be in the root of your standalone package directory.

Specify the Connection Info for your database.

Click “Create Command” to generate the vsdbcmd command for this operation.  Once you’ve generated the command, you can click “Run Command” to run the command.

Summary

VsDbCmd is a great feature in Visual Studio 2008 Database Edition.  Hopefully, this UI will make it easier for you to use it so that you can manage your database schema development and deployment just like the rest of your software development efforts.

(BTW, if you find any bugs – and you probably will – please let me know by email.  info@benday.com)
Update (9/30/2009): If you run in to problems running VSDBCMD like the null reference exception problem and some other problems with missing pre-requisites on some machines, here’s a link to some of the additional things you might need to install.

-Ben

-- Looking for help adopting Visual Studio Team System 2008 or Visual Studio Team System 2010?  Need training?  Want someone to coach your Team System project?  Need help installing TFS?  Want to customize your Team System development process?  Contact us via http://www.benday.com or info@benday.com.