July 9, 2012 at 12:32 pm
When using the copy database wizard to copy a database from one instance to another (via SMO, not detach/attach) it runs for ten minutes or so before failing. From the error log I can see it creates and successfully completes many, many individual packages (which I assume are one per database object). However it fails on one particular table and gives the error:
SQLISPackage100,Error,Event Name: OnError Message: ERROR : errorCode=-1073548784
ALTER TABLE [dbo].[MyChildTable] WITH CHECK ADD CONST..." failed with the following error: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyFK".
The conflict occurred in database "MyDestinationDB"<c/> table "dbo.MyParentTable"<c/> column 'MyFKField'.".
I've verified the source data to ensure that all the values in the child table for the fk field are valid records in the parent table as pk values.
Here's the kicker: when I copy the source database to a second environment, restore it, and run the same copy database wizard it runs successfully. Any ideas why it may report the bogus error and how I can resolve it on the first environment?
July 9, 2012 at 12:49 pm
Two thoughts. Is the DB in use at the time the copy is run? If it's using nolock (or something is disabling the foreign key) that would explain it. The other is that for some reason the content of the tables isn't being copied in the right order.
July 9, 2012 at 12:59 pm
cfradenburg (7/9/2012)
Is the DB in use at the time the copy is run?
Source DB is in (light) use. The destination DB is always dropped and recreated as per the configured setting in the wizard.
Unfortunatly I spotted an error in the original post:
The conflict occurred in database "MySourceDB"<c/> table "dbo.MyParentTable"<c/> column 'MyFKField'.".
should read:
The conflict occurred in database "MyDestinationDB"<c/> table "dbo.MyParentTable"<c/> column 'MyFKField'.".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply