• Deploying your database from the command line

    Published by on February 7th, 2012 10:01 am under database

    1 Comment

    The other day I was ask to introduce the Web Deploy Tool to automate the deploying of our deliveries in our QA environment. Web Deploy by itself is a great tool, has some pitfalls when it comes to configuring it correctly, but that’s a story for another post :)

    One of the many abilities provided by Web Deploy is the chance of publishing your database with your website, utilizing the Publish interface provided by Visual Studio. This interface allows the user to also deploy a database schema/data and the ability to run a database script on the target server. I was looking for an automate way of deploying only the latest changes on my database, just the same as what you can get from running a “Schema Compare”. This, sadly, cannot be done (at least OOB). Don’t get me wrong, this is not a Web Deploy limitation, this is the default behavior of Visual Studio.

    Working around it

    My goal was to have at least a command line script that could give me a differential deployment of our database. I already had TeamCity deploying our builds with WebDeploy, and I certainly didn’t  want to deploy our database by hand. Googling around I met the EXE file in charge of performing the gracious schema compares you perform through the Visual Studio UI, “vsdbcmd.exe” (it can be found on installations of VS Ultimate Edition/Premium)

    According to MSDN,

    You can use VSDBCMD.EXE to:

    • Import a database schema from a live database into a .dbschema file
    • Generate a deployment script from a .dbschema file
    • Generate a deployment script from a .dbschema file and deploy that script to a target database

    Generate a deployment script by comparing two .dbschema files.

    Great! Let’s see this working!

    vsdbcmd.exe /a:deploy /dd /dsp:sql /model:"\path-to-dbschema\MyDabtase.dbschema" /cs:"Data Source=MyServer;User ID=User;Password=password;Database=MyDatabase;" /p:TargetDatabase="MyDatabase"

    Easy enough right? The former command will take the dbschema pointed to, compare it with the targeted database and deploy the changes!

    As a final step, I’ve added a new  task to our TeamCity build in charge of deploying our database to our QA environment, works wonders!

    References

    Tags: , ,

    • jmo

      A bit down the line, but how did you deal with the space in “Data source” in your Team City task command line?

      Mine barfs at the space, I’ve tried all sorts of quotes, using a configuration parameter etc.