The long and short is that I place everything in a separate script and it get's shared (to an "InFlight" folder) or branched (To a specific release).
Each script is short and simple and does one thing, so a create or alter table is in its own file. A stored proc is in its own file. A FK is in its own file.
At the end of each file, there is a select statement that verifies object creation (or not) and lets me know that where in script execution I am in the error file.
- Security is placed in each file, so I set the object security inside the table, view, procedure file.
- Defaults are in the table script.
- data updates are in files and may be shared. Depends on the data update. If they are related, I may include 2 or 3 in the same file. I separate each with a GO and include "select 'something happened'" before each GO so I can tell where an error occurred.
Last week, I did the following:
- Altered 2 sprocs. As soon as the changes were checked into VSS, one was shared to "InFlight" and one was branched into the release project for the week. I knew one change would be released, but wasn't sure about the other.
- I created a new table. The script was branched to the release project. this included a couple defaults bound to the columns and the GRANT statement for the table in the script.
- I altered a table. The alter script was shared to the Inflight project and I manually checked out and updated the original table create script.
- I had a script to add data to the altered table (above). Shared to InFlight.
At this point, a developer completed work on the altered table. I then moved them and branched them to the release project. I deleted them from the InFlight folder.
I created my 0Upload.cmd file for the release project and checked out all scripts. This was applied to the QA server and I verified that all items were branched.
QA found a bug in the procedure. I then deleted it from the Release project, checked it out from the sprocs project and fixed the issue. It was checked in and rebranched to the release folder. I then manually ran this on QA.
Once QA verifies everything, the release project is applied to the live system in the same way as it was applied to the QA system.
If I hot fix something, I usually do follow the same procedure and make a releasedate-HOTFIX project. This is really for tracking more than anything.
There are lots of manual items here, but it has worked flawlessly for over 2 years. If it runs on QA without errors (and this may take testing) it has ALWAYS worked on production. The important things I learned over time were the orders in which to apply things.