Archiving data

You could possibly use transactional replication for this.
All the tables would be in the same publication and
relationships may also be registered there. Foreign keys
would be marked as Yes (Not for Replication). You'd have
to ensure that deletes are not replicated (using None for
the replication command).
Initially I mentioned 'possibly' - this is because there
may be some deletes that you really want to be replicated -
it all depends on how your system is set up. EG on one
database I worked on, there were no deletes allowed -
updates of a bit flag would effectively remove the record
from the user's vision. If your system is like this then
transactional would be ok. If not, then DTS, or snapshot
replication to append records could be used before the
deletes take place.
HTH,
Paul Ibison
I agree with Paul. I would incorporate the business logic for deletes in
custom stored procedures.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:17eb01c46f48$46da5a20$a301280a@.phx.gbl...
> You could possibly use transactional replication for this.
> All the tables would be in the same publication and
> relationships may also be registered there. Foreign keys
> would be marked as Yes (Not for Replication). You'd have
> to ensure that deletes are not replicated (using None for
> the replication command).
> Initially I mentioned 'possibly' - this is because there
> may be some deletes that you really want to be replicated -
> it all depends on how your system is set up. EG on one
> database I worked on, there were no deletes allowed -
> updates of a bit flag would effectively remove the record
> from the user's vision. If your system is like this then
> transactional would be ok. If not, then DTS, or snapshot
> replication to append records could be used before the
> deletes take place.
> HTH,
> Paul Ibison