How do I manage version control when developing with SQL Server Express?

Question

I am developing a website using SQL Server Express on my development machine. My web hosting company is providing me with SQL Server 2005.

At the moment all I have is a database that I develop with and a database that is on the live server. I do not have the original scripts to generate the schema but I can auto generate the create scripts individually or for the entire database.

I am now putting my code into source control and I would like to know how I manage my database schema. What do I put into it? Create commands? Alter scripts?

The database is very small at the moment and it is not hard to maintain the two databases, but I am concerned that going forward but it will get out of hand. Do you have any tips for getting the live database in sync when deploying new code?

EDIT Any ideas as to what should go into source control? Should the DDL scripts go here?

Answer 1

Deploy schema changes as DDL upgrade scripts and, if you haven't already, add a table to contain the schema version number which you update at the end of each upgrade script.

EDIT: Yes, all your scripts should go into source control, including DDL scripts.

Answer 2

I typically keep a testing copy of the live database on my local or virtual development box, which I flush routinely from the prod database down to testing. The testing copy is meant for my total exploitation. When I have something that I believe is ready for deployment, I move it to my development dataset, which mirrors the prod db and is not used for playing around. If the development db passes all my tests, I deploy the script to the production db