Sunday, February 26, 2012

DTS-copy without dropping indexes?

When copying all tables of a database from a server to another using DTS, is it crucial to drop the indexes before copying?
If "just" a performance issue, is there any other way to postpone index updating until the DTS has finished copying?This depends on the type of data transfer you choose.

if you choose to append records to destination tables it's not necessary to drop indexes.

if you choose to drop tables the correlated indexes are dropped too.

if you choose to copy objects from one DB to another usually existing objects (tables and indexes ) are dropped.

If indexes exist it's not possible to stop index updating, I think.

bye|||What I intended to do was to copy a database from one instance to another this way:

- 'alter table nocheck constraint all' on every table
- 'truncate table' on every table, to make sure that no data from before DTS-copying can be left.
- DTS (which replaces data, not appends)
- 'alter table check constraint all' on every table

However, I now realized Truncate Table requires the Foreign Keys to be dropped, disabling them through Nocheck is insufficient.

No comments:

Post a Comment