Saturday, November 6, 2010

Database Projects in VS 2010 - Deploy database with values in master tables

Recently went across a situation where I need to deploy a database in SQL Server 2008  using database projects of VS2010.Things were simple since when you build solution with  database projects in VS2010 it creates     db schema,db deploy  manifest file, db script file etc.When database project is deployed using VS 2010 it uses deployment API available in .NET.The link below describes how you can achieve your own solution

The same functionality can be achieved using sqlcmd.You can execute these sql scripts created using sqlcmd (sqlcmd gets installed when SQL Server 2005/2008 is installed).

I guess same results can be achieved with VSDBCMD also.Never tested that :-).

Now things looked challenging when I had to insert data in some tables  while deploying the database projects.In simple words if the database is deployed it should create database,database objects also it need to insert some data in master tables.
Thereafter I found out you can put all your insert statements in Post Deployment Script File.The database projects in VS 2010 will have a file Script.PostDeployment.sql , just put your insert or delete  statements in that file and when you deploy the project it will insert data in master tables.


1 comment: