We help IT Professionals succeed at work.

sp_attachdb and cascade delete

frankytee
frankytee asked
on
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.
 
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
attaching a database will niclude everything that is contanied within that database.
With v7 thnigs are pretty self contained so you do get all permissions and objects within the database.
(jobs are held in msdb so the user database will not contain those).

Two problems.
Permissions are for users within the database mapped from the logon_id held in master. If this is another server and the logon_ids are different then the user permissions will not map correctly.

Any other databases that reference the one that has been attached may not realise that anything has happenned so any query plans held may now be referencing the wrong objects and get wrong data or corrupt the database.
To fix this either recompile all the objects in the database or bounce the server (or make sure databases are self contained).

as miron says cascade delete is not available in v7 you will have to code it yourself.

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.