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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior 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
stejamoAuthor 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.