Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a production database which has been plugging away for several months. I have been making modifications to a duplicate copy on a test/dev server. I've added some additional fields (null allowed) to several tables, as well as I've added some new tables and indexes to both new and existing tables.

My question is this: I am ready to take my test/dev database and push it into production. The problem is that I need to migrate all the data from my production database to my test/dev database. My first thought was to use SSMS to "IMPORT" data from production to test/dev, the problem I'm having is that even with "enable identity insert" I still get all types of foreign key constraint errors. This is a bit of a two-parter

  1. Is there a better way to migrate schema changes from test/dev to production than the process I just outlined?
  2. If not, is there a way to temporarly disable all insert constraints during the import?

I would be very happy to only migrate my test/dev schema to my production server, but I have not found any way to do this without manually going table by table, index by index, view by view, sproc by sproc... etc. I've seen some tools that claim to be able to do it, but I'm looking for a free/open source solution and haven't found anything.

note: database is small < 10gb, the test/dev database is fully truncated with zero records in every table.

share|improve this question
You may be able to use the trial version of RedGate's SQL Compare(or a similar product) if this is a one off thing. – DaniSQL Dec 23 '11 at 22:47
up vote 3 down vote accepted

It sounds like you've got this entirely back to front and upside down. Typically changes are tracked using scripts, which you would then apply to the production database. I've never encountered a case where it made sense to import production data to the next version created from development.

If your database isn't currently in source control, the articles linked to from Jeff Atwood's article Get Your Database Under Version Control are a good introduction. Also, the free Redgate ebook SQL Server Team Based Development includes a chapter on source control for databases.

If you've been making changes via GUI tools and don't have any record of what's changed your best bet is a database comparison tool, like Redgate SQLCompare or Apex SQLDiff. These tools will generate the scripts to upgrade your production database to match the schema of development.

If the boss won't part with the cash for a comparison tool, you could reconcile the changes manually using a diff tool like WinMerge or DiffMerge. Use SSMS Generate Script tools to script objects to individual files, then use the diff tool to identify differences. Finally, hand craft the necessary ALTER statements for any changes to tables.

share|improve this answer
Yes. I have made 95% of the changes in SSMS... I realize this is sub-optimal. – Nate Dec 22 '11 at 20:22
Not so much sub-optimal, just a bit risky when those changes aren't being source controlled. If you're more comfortable with GUI changes, continue to do so but use the option to generate a change script for you. – Mark Storey-Smith Dec 22 '11 at 20:25
This looks great for going forward, had no idea that option existed. Any tips for sailing out of where I am now? – Nate Dec 22 '11 at 20:41
Comparison tool would be the safest (least error prone) and quickest option. I'll edit in a manual approach to my answer. – Mark Storey-Smith Dec 22 '11 at 20:44

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.