Tell me more ×
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. It's 100% free, no registration required.
up vote 0 down vote favorite

I try to copy a database from 1 server to the other to build a staging environment. I do this with SSID. First I delete the existing database on my staging server, create a new and empty one and then I run the script. I copy everything. Most of my databases copy correctly except for 1 which gives me a Foreign Key constraint error. What can I do to fix this? I tried googling, but without any results.

   SSIS package "XXXXXXXX.dtsx" starting.
Information: 0x4002F418 at MyDatabase, Transfer SQL Server Objects Task: There are no Logins to transfer.
Error: 0xC002F325 at myDB, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "ALTER TABLE [dbo].[TemplatePropertyValue]  WITH CH..." failed with the following error: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_VALUE1_Template". The conflict occurred in database "myDB", table "dbo.Template", column 'Id'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".
Task failed: myDB
SSIS package "XXXXXXXXX.dtsx" finished: Success.
share|improve this question
That would indicate there is a foreign key relationship between two of your tables and the transfer object task is picking the "wrong" one to populate first (or the RI is not enforced in the original system, but seems unlikely). I don't work with the transfer object task so I'm no expert. It doesn't seem to offer a hint on object ordering. Perhaps leave CopyForeignKeys as False and then rerun with only CopyForeignKeys as True. Or, make CopyAllObjects False and then enumerate everything but the failing table and then have a second transfer task to copy the failing table by itself. – billinkc Jul 13 '12 at 16:03

migrated from stackoverflow.com Jul 16 '12 at 2:31

1 Answer

up vote 0 down vote accepted

AS sugested by Billinkc we tried to first copy the table and then copy the relations. This worked as hooped.

share|improve this answer

Your Answer

 
discard

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.