I've been focusing on a task and become it with the first stage. However, the requirments wound up altering and I must add new tables and redo a few of the foriegn key references within the DB.
The issue I've is my insufficient understanding of coping with carrying this out type of switch to a staging then production database after i obtain the development done on dev database.
What exactly are some methods for moving database schema changes and looking after data within the database?
About so far as my understanding is on carrying this out is open Sql Server Management Studio and beginning adding tables by hand. I understand this really is most likely a poor method of doing it so searching for how to get it done correctly while recognizing I most likely began out wrong.
You need something known as like a "Package". Clearly, if you're maintaining some type of a resource control, all of the scripts for that changes that you simply do within the development conditions ought to be maintained within the source control configuration tool.
When you are done with the scripts/changes that you simply deem licensed to maneuver to next greater atmosphere. Prepare the package with getting each one of these scripts in folders (ideally categorized as Methods, Tables, Functions, Bootstraps) After which possess a batch files that may execute these scripts within the package inside a particular order using OSQL command line utility.
Have separate batch files for UAT/ Staging/ production to ensure that you can easily double click the batch file to complete the package within the appropriate server. Look for OSQL options.
By doing this all of your conditions have been in sync!
I typically use something similar to the SQL Server Posting Wizard to create SQL scripts from the changes. That's an extremely easy and simple approach. The main downside with this tool would be that the created will drop and recreate tables that aren't transformed but utilized by methods which have transformed (and that i can't realise why), so there's some manual work involved with studying the script and take away stuff that don't have to be there.
Note that you don't have to download and install it you are able to launch it from inside Visual Studio. Right-click an association within the Server Explorer and choose "Publish to Provider" within the context menu.
You will find some tools available that will help you with this.
It enables you to develop a model in the dev/integration database after which (for a lot of, although not every case) instantly create scripts which improve your push database using the changes you've made to dev/integration.
For Versus 2008, make certain you receive the GDR2 patches.
You might like to take a look at something like Liquibase: http://liquibase.org/
Red-colored Gate SQL Compare and SQL Data Compare completely. Since my opportunity purchased, it saved me a lot of time staging our databases from DEV to check to ACCEPTANCE to PRODUCTION.
And you will get it synchronize having a scripts folder too for simple integration inside a source control system.