4

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, stored procedure by stored procedure... 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.

Improve this question
1
  • 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
    Commented Dec 23, 2011 at 22:47

2 Answers 2

Reset to default
5

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.

Improve this answer
4
  • Yes. I have made 95% of the changes in SSMS... I realize this is sub-optimal.
    – Nate
    Commented Dec 22, 2011 at 20:22
  • 1
    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
    Commented Dec 22, 2011 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
    Commented Dec 22, 2011 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
    Commented Dec 22, 2011 at 20:44
0

For these types of migrations i suggest you take a look at schema and data synchronization software. They usually have special logic that deals with table constraints during synchornization. SQL Server offers SQL Server Data Tools which, for basic synchronization tasks, will get the job done. I haven't tried anything too complicated with these tools so I can't really say what their limts are.

If you are open to third party software I suggest you take a look at xSQL Schema Compare for schema synchronization and xSQL Data Compare for data synchronization. They have a ton of options which you can enable or disable (including foreign key constraints or identity inserts) to customize the synchronization process to your liking.

Hope this helps!

Disclosure: I'm affiliated to xSQL

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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