Method for Archiving Rows

I need to archive some rows from a production database. Any ideas on good
principles to follow ? I would absolutlely want to ensure that each row is
properly archived prior to deleting it from the production table. Anyone
have ideas that they used and wish to share ?
ThanksRob wrote:
> I need to archive some rows from a production database. Any ideas on go
od
> principles to follow ? I would absolutlely want to ensure that each row
is
> properly archived prior to deleting it from the production table. Anyone
> have ideas that they used and wish to share ?
Do the archive process during "off-hours."
Wrap the append (to the archive) and delete (from the production db)
statements in BEGIN TRANS and COMMIT. Use ROLLBACK if an error occurs.
See the BOL for more info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks for responding...
Something I was thinking about was linking the key from the "archive db" to
the Key to the "poduction db" and updating a flag on the prodcution. This
would ensure that the row existed in the Archive prior to deleting from
production.
"MGFoster" <me@.privacy.com> wrote in message
news:0F1Yd.6545$cN6.2489@.newsread1.news.pas.earthlink.net...
> Rob wrote:
> Do the archive process during "off-hours."
> Wrap the append (to the archive) and delete (from the production db)
> statements in BEGIN TRANS and COMMIT. Use ROLLBACK if an error occurs.
> See the BOL for more info.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||Rob wrote:
> Thanks for responding...
> Something I was thinking about was linking the key from the "archive db" t
o
> the Key to the "poduction db" and updating a flag on the prodcution. This
> would ensure that the row existed in the Archive prior to deleting from
> production.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You could update that flag after the archive process runs, but, again,
that can be done inside the transaction. You don't need to "link"
anything between the archive & production. IOW, the production table(s)
would have an "Archived" column. The archive process would only select
rows that do not have the Archived column set to true.
An example of the process:
begin tran
insert into archive_table (<column list> )
select <column list>
from table_name
where archived = 0 -- False will be zero & True will be 1
-- the default for archived should be zero
if @.@.error <> 0 goto err_
update table_name
set archived = 1
where archived = 0
if @.@.error <> 0 goto err_
commit
goto exit_
err_:
rollback
exit_:
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjCzOYechKqOuFEgEQJu+gCePIIvgGgSjDh3
9lQY0uy3JVrybfYAoJTV
xPUUKn7k08dcCAr42/VJI7zf
=pp/t
--END PGP SIGNATURE--|||I just copy the records to the ARchive DB and then my purge solution does an
inner join to the Archive table (Or WHERE Exists()) to ensure existence
prior to deletion from prod.
works like a champ as long as proper indexes are in place, etc.
GAJ