Use Sql server Publishing wizard to keep track of the change of the database structure

One of the most important procedure in software development, is keeping track of every change in the project, to accomplish this task we have source control system like subversion or cvs. When you develop application that are based on a database, it is fundamental that you do not loose track of the database structure, but a source control system is not designed to keep track of database evolution. In this situation you can use the Sql Server Database Publishing Wizard to automate a task that periodically create the script to regenerate all the database and then update the subversion to store that version. After you have installed the Sql server database publishing wizard you can create a simple bat script.

"D:\Program Files\Microsoft SQL Server\90\Tools\Publishing\SqlPubWiz.exe" script -C "data source=localhost\sql2005;uid=xxx;pwd=xxx;initial catalog=mydatabase" Schema.sql -schemaonly -f -targetserver 2005 svn commit --username svnuser --password xxx -m "AutomaticUpdate" Schema.sql >update.log

I create a directory in the SVN project structure called DBScripts, then I create a empty Schema.sql file, add it to the subversion, then add the .bat file shown above, and finally I schedule the server where I have the to run this script once a day. The game is done.



Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP