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-dbschemaMyDabtase.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!