How do you Move Dev Database Changes to Production Database?

Question

I have been working on a project and gotten it through the first stage. However, the requirments ended up changing and I have to add new tables and redo some of the foriegn key references in the DB.

The problem I have is my lack of knowledge of dealing with doing this kind of change to a staging then production database once I get the development done on dev database.

What are some strategies for migrating database schema changes and maintaining data in the database?

About as far as my knowledge is on doing this is open up Sql Server Management Studio and starting adding tables manually. I know this is probably a bad way to do it so looking for how to do it properly while realizing I probably started out wrong.

Answer 1

For maintaining schema changes you can use ApexSQL Diff, a SQL Server and SQL Azure schema comparison and synchronization tool, and for maintaining data in the database you can use ApexSQL Data Diff, a SQL Server and SQL Azure data comparison and synchronization tool.

Hope this helps

Disclaimer: I work for ApexSQL as a Support Engineer

Answer 2

You have to have something called as a "KIT". Obviously, if you are maintaining some kind of a source control, all the scripts for the changes that you do in the development environments should be maintained in the source control configuration tool.

Once you are done with all the scripts/changes that you deem certified to move to next higher environment. Prepare the kit with having all these scripts in folders (ideally categorized as Procedures, Tables, Functions, Bootstraps) And then have a batch files that could execute these scripts in the kit in a particular order using OSQL command line utility.

Have separate batch files for UAT/ Staging/ production so that you can just double click on the batch file to execute the kit in the appropriate server. Check for OSQL options.

This way all your environments are in sync!