Backup Db from ServerA to ServerB

[TRKneller]

I have 9 SQL servers in a group. Eight of them back up to the ninth nightly. The ninth then backs up the DBs to file and finally to tape.

However, using DTS export from server 1 to server 9 I often get a failure error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Question_Conten_45BE5BA9'

Now, this only happens on two of more than 10 different DBs. I'm learning this as I go, but really need some help. What would be the appropriate steps to fix this or achieve my goal?

Thanks,
TRKneller

 

[Catadmin]

You need to locate in your DTS job what part of it is trying to Alter a table without dropping the Foreign Key constraint.

Just in case you don't understand the difference between Primary Keys and Foreign Keys, here's a brief synopsis.

Primary Keys are the (usually) unique clustered Index on a table so that each record can be told apart from the other records on the table.

Foreign Keys are (usually) a reference to the Primary Key of another table.

A good example would be a Product Table and a Sales table. The Sales table will contain a record of item sales including price, quantity and a "ProductID" column which references back to the Product table (which contains the ProductID, ProductName, etc.).

If you don't drop a Foreign Key reference (or constraint) before you do an Alter Table statement, you won't be able to Alter the table. The constraint is there to maintain referential integrity so you don't end up with orphaned records.

Make sense?

So, just locate the part of the package where the alter table statements are, insert a Drop Constraint statement, then, after your Alter Table, insert a Create Constraint statement to replace the foreign key reference you just deleted.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???