How can I generate a script of my database as it is?

Question

My primary reason for this is to keep track of database schema changes for my application. In SQL Server Management Studio I am able to generate a create script which creates the database but it doesn't contain any test data. Ideally when the script is run it should DROP the existing database (assuming it already exists) and then recreating it using this new script containing schema changes and test data from my development machine.

So how can I generate a script that will create a database with all the tables, stored procs, triggers, views, test data, etc?

I've tried using the import/export functionality but that's no good because it doesn't seem to copy over stored procedures. Plus it would be nice to have a script so I can track changes to the schema using mercurial.

I am using SQL Server Express 2008 R2 along with SQL Server Management Studio.

Answer 1

Try Microsoft SQL Server Database Publishing Wizard. This is a powerful flexible tool for scripting schema / data rom SQL Server.

Answer 2

Personally I use Microsoft Visual Studio 2010 Database Project with a Source Control Repository (SVN) to track changes to the schema.