I have step in my package that copies database from the server to my local
machine.
I am doing it so I can back up database - I am using hosting database.

The problem is that coping of data fails if I specify in options that I want
to copy Primary and Foreign keys. I would like to copy that also in order to
have ready to restore back up.
Is there a way to turn off keys after schema was copied but before data is
loaded?
Thank you,
Shimon.

Hi Shimon,

Welcome to use MSDN Managed Newsgroup!

You could use NOCHECK instead of UNCHECKED when altering a existing
constraint to be invalid. I made a sample on my side with DTS and it works.
Here is a sample

CREATE TABLE Test2
(
TID INT Primary KEY,
CID INT IDENTITY(1,1) UNIQUE
)

INSERT Test2 VALUES (11)
INSERT Test2 VALUES (12)
INSERT Test2 VALUES (13)
INSERT Test2 VALUES (14)

--SELECT * FROM Test2

CREATE TABLE Test2Child
(
TCID INT FOREIGN KEY REFERENCES Test2(CID),
TC CHAR(1)
)

INSERT Test2Child VALUES (55,'B')
--The statement above will fail
ALTER TABLE Test2Child NOCHECK CONSTRAINT FK__Test2Child__TCID__48CFD27E

INSERT Test2Child VALUES (55,'B')
--The statement above will do since we have altered NOCHECK
--Use a DTS package to do the test, it also works

NOTE that if you have specified NOCHECK for the constraint, it will not
check the constraints any more.



Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Michael
Do you suggest me to write the script for each table to disable keys?
It is not really a solution since I have over 100 tables. Plus the schema
changes and my script can become invalid.
Is there something that can disable all the keys whatever they are?
An if such thing exists how can I out it in the DTS package together with
backup and data transfer tasks?

Thank you,
Shimon

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote

You can create a task that will create a script (or even a Stored Proc) that
will disable and then reenable the constraints. You can generate both
scripts by the below resultset:

SELECT 'ALTER TABLE ' + TABLE_NAME + ' NOCHECK CONSTRAINT ALL' FROM
INFORMATION_SCHEMA.TABLES

SELECT 'ALTER TABLE ' + TABLE_NAME + ' CHECK CONSTRAINT ALL' FROM
INFORMATION_SCHEMA.TABLES

Scott

"Shimon Sim" <shimonsim048 (AT) community (DOT) nospam> wrote

This look cool.
I have to try it.
Thank you.

"Wm. Scott Miller" <Scott.Miller (AT) spamkiller (DOT) wvinsurance.gov> wrote in
message news:O7sUD7u6FHA.1416 (AT) TK2MSFTNGP09 (DOT) phx.gbl...