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.