Generate SQL Server Scripts With Visual Studio

So for about 3 months now I've been working at an IT consultancy as a an analyst/programmer working mainly with Micro$oft technologies. They're far more powerful than I care to admit, but as soon as I get the chance to jump ship back to Unix development, I probably will.

A little useful hint came up recently, however, that might be of use to random googlers. So here we go. I'm currently developing a web app for an un-named client, which must interface with a SQL Server 2008 database.

During the course of developing the web app, I've made ad-hoc changes to the database and not adapted my SQL scripts and raw data files at the same time (bad practice I know, but what's done is done).

But not a problem, surely I can just use SQL Server 2008 Management Studio to generate new SQL scripts to create and populate the database? Well, maybe you can, but I couldn't find it. A quick google suggests this feature has been removed, at least from the 2008 Express Edition.

However, there IS an option to produce these scripts using Visual Studio Web Developer (See the screenshot below, suitably blurred just in case anyone actually cares about the origin or nature of this work).


In the app properties menu, in the Package/Publish SQL tab, you can select "Data only", "Tables only" or "Tables and data" from the Database Scripting Options menu. Make sure that you do this with the correct database selected towards the top of the pane (in the Database Entries box). When you come to publush your app, Visual Studio will then produce useful scripts that can be used by colleagues or the sys admin on-site to reproduce the database exactly as it is on your dev machine. Job done.