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
- Is there a better way to migrate schema changes from test/dev to production than the process I just outlined?
- 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.