Best way to move database from test environment to production environment (SQL Server 8)

This should be easy because I'm sure it's an everyday kind of task.  I've just never had to do it before.  I am using an .adp project with SQL Server 8.  A couple of months ago I made a copy of one of our production databases in order to make some changes.  The changes involved major table restructuring for a few tables, some new tables, procedures, views and forms.  We used this same development database for testing also.  Now I am ready to implement the new changes.  What's the best way, given that database activity has continued in the production environment?  

Basically, there are about five tables in the production environment that have continued to be updated.  Seems like the simplest thing to do would be to move just those 5 tables over to the test environment and then make the test environment the new production environment.  But I'm not sure how to do this and retain the contraints etc.  Can I just empty said tables in test database and export/append to tables from production database?  But the constraints ...  Do I have to remove constraints and recreate them (or I guess I could generate a script) afterwards?  Fortunately, these tables are not the tables that underwent restructuring.

Or would it be best to try to bring all my changes from test over to production?

Or is there a prescribed way to accomplish all this that I don't know about?

Thanks for your help.
stejamoAsked:
Who is Participating?
 
stejamoConnect With a Mentor Author Commented:
Okay, do I get to answer my own question?  haha.  :)  

Scott,  I do appreciate you recommending the RedGate tool and i will probably end up buying someday, but it was a bit more than I really needed at the moment.  Regarding your second paragraph, I already had the alter table scripts ready to go.  It was just a matter of moving the actual data.

I'll tell you what I ended up doing and maybe my question will make more sense.  Instead of moving the changes I made in the test environment to the production environment, I decided to make the test environment BE the new production environment. As I mentioned before, I knew which tables had continued to be updated in production, so it wasn't a matter of synchronizing the entire database.  What I did was delete all records from the destination tables (and their children) in the test environment.   Then I used DTS Import/Export in SQL Server Enterprise Manager to import from production to test.  I had to do them in just the right order so as not to violate any constraints. I did end up having to delete one contraint and recreate it afterwards ... but only one.   The main thing I figured out was to check off "ENABLE IDENTITY INSERT" under a little button in the Transform column.  That way I was able to retain the same ID numbers.  

So that's all it was.  Simple and only a little tedious, since there were so few tables involved.  
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
There are several third-party tools that will compare different dbs, even of different servers, and generate SQL DDL to update the second db to match the first.  For example, I think RedGate has such a tool.

If you don't want to purchase those and/or get a free trial, you could use Enterprise Mgr to script out both tables and the compare the scripts using a standard -- and totally free -- line-compare tool.
0
All Courses

From novice to tech pro — start learning today.