October 27, 2004 at 5:17 am
I'm trying to use DTS to copy all data/objects/SPs/views to a different server, basically copying the production data to my development box to update it.
I keep getting a failure on some tables regarding Foreign Key constraints.
How do I do this? Is there a certain order I need to follow?
I'm currently using the Copy Data and Ojects option.
Any tips?
Thanks!
October 27, 2004 at 5:22 am
See, if this helps:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 27, 2004 at 12:02 pm
You'll either have to disable the foreign key constraints on the destination tables before loading the data or you'll have to load the data in a specific order i.e. parent tables, then child tables. Either way, you'll probably need to use a package with an Execute SQL task and a Transform Data task rather than a Copy Objects task.
We use a slightly modified version of a script found on this site to list the tables and foreign key dependencies in order.
http://www.sqlservercentral.com/scripts/contributions/445.asp
Greg
Greg
November 2, 2004 at 3:28 am
Greg is correct - I would disable any constraints and triggers on the destination db before moving the all data/objects/SPs/views. Below is a starting point where you will need to generate a set of script to get all the sysobjects.name (xtype = 'u') of the destination database and run the 2 sets of scripts - no specific order.
1. ALTER TABLE sysobjects.name NOCHECK CONSTRAINT all
2. ALTER TABLE sysobjects.name DISABLE TRIGGER all
PLEASE PLEASE REMEMBER TO RE-ENABLE ALL CONSTRAINTS AND TRIGGERS FOR EACH OF THE TABLE AFTER THE MIGRATION.
Hope this helps and if you need further help, please send me a note. I am not an expert but I have done quite a few data migration from Sybase to SQL7/2k
Herb
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply