sp_attachdb and cascade delete
2 quick questions relating to copying a database from one
sql server 7 to another sql server 7.
1) Does the sp_detachdb and sp_attachdb procedures
copy all the objects/permissions/jobs etc? I've tested
it and it seems to do so but I can't find any ms doco
on it.
2) After I copy the database, I want to remove all the
unwanted records. Since there is about 50 tables
involved, I just want to delete the records in the main
table (tblClient) but I cant find much info on cascade
delete in Books Online.
For eg, if tblClient has clients A, B, C, D, E and F.
I want to remove clients A, B and C from the original database on Server1, and remove clients D, E and F from the newly created db on Server 2.
Is there a simple t-sql command which I can use in query analzyer to enable cascade delete and then simply delete the unwanted client from that one table and thus from the remaining tables? I want to avoid creating triggers and manual deletion etc.
sql server 7 to another sql server 7.
1) Does the sp_detachdb and sp_attachdb procedures
copy all the objects/permissions/jobs etc? I've tested
it and it seems to do so but I can't find any ms doco
on it.
2) After I copy the database, I want to remove all the
unwanted records. Since there is about 50 tables
involved, I just want to delete the records in the main
table (tblClient) but I cant find much info on cascade
delete in Books Online.
For eg, if tblClient has clients A, B, C, D, E and F.
I want to remove clients A, B and C from the original database on Server1, and remove clients D, E and F from the newly created db on Server 2.
Is there a simple t-sql command which I can use in query analzyer to enable cascade delete and then simply delete the unwanted client from that one table and thus from the remaining tables? I want to avoid creating triggers and manual deletion etc.
miron
I think that casecade delete was introduced in SQL Server 2000. You will need to create triggers to enforce referential integrity during delete. Also, though copied properly, sql users/logins need to be re - freshed using EM in the destination database copy, or dropped and re - created programmatically.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hard to believe that sql 7 does not have cascade delete considering humble Access has had it for years!
Thanks both for responding, i gave the pts to nigel for the extra info which helps my understanding.
Thanks both for responding, i gave the pts to nigel for the extra info which helps my understanding.