We have a long running project and we have continuous database scripts included in the project. Currently we use a manual way to run those scripts which I think does not work in a long run and the repository which saves these files only gets enormous. For this we use AliaSQL.
With many out of the box VS tools database projects available I think this process should be relatively simple and efficient.
Can someone highlight the best practices for maintaining database scripts for any project. Which includes changes to the scripts (table, view, stored procedure etc.)
Cheers!
I write my experience that might help.
I was working on a big team. All developments were going though test process. After passing the test, the configuration management team were running all the scripts involved with that task on a REFERENCE database.
This way at the point of deployment we had an up-to-date database structure and any initializing data.
Then you have 2 options for deployment
comparing the reference database form current development period with the previous and generate a change script (in-site in your company)
If you publish patches and service packs during production cycle (which means each customer may have different data structure), you can ship the reference database with an installation program and compare the customer database with reference database on-site (at customer location) and apply required changes (both data and schema).