Is there a command line to deploy a SQL Server database project?

Question

We are using the SQL Server Database project template with Visual Studio 2010. As part of our integration testing, I would like to first launch a fresh deploy of the database. But short of manually right clicking the project and picking "Deploy", it doesn't seem to be a simple thing to get a fresh copy of the database deployed.

The documentation for this project type seems sparse to non-existent. Perhaps I"m looking in the wrong place. If possible, please include a reference to the how-to.

Update:

Our integration tests are written as unit tests in Visual Studio. So the goal would be to press the Run Tests button, and have the database deploy, and then the tests against it run.

Answer 1

I managed to find a command line tool to do this. It's called VSDBCMD.EXE and it comes with Visual Studio 2010. I first learned about it here (scroll to the bottom of the article), then I followed a link and found the details of how to get it installed on my server.

In the second article, if you're on a 64 bit machine, you won't find the VSTSDB directory under Program Files... you'll need to look under Program Files (x86).

I followed the instructions to the letter (installed both the 32-bit and 64-bit version of SQL CE) and it's working great. I basically take the output of a build from my SQL Server 2008 database project, zip it up and put it on the server, then run the deploy script.

The first time I did this, the target database didn't exist. The tool created the database and set it up nicely. Then I tried adding a column to a table in my project. I rebuilt the project and took the output and deployed as before, and it correctly altered the existing table, just like it would have done by right-clicking and selecting Deploy.

Seems to be working well so far. Hope this helps!

Answer 2

For a fresh database instance (not an incremental update to a database), MSDeploy should do the trick. Here's a quick post I found on the subject. I haven't actually done it myself yet, but a collegue says it's worked in his environment.