Hi!

The database is growing with a lot of old logging rows...
We would like to keep the production environment nice and clean - but still
keep old data in a specific archiving database if specific need is raised.

How do I in the most easy and streamlined way set up DTS to move/archive
(COPY & DELETE) rows from "production" tables (i e source) to equivalent
"archive/history" tables (destination).

Note:
* Only rows older then one year should be archived (timestamp <
DateAdd(yyyy,-1,GetDate())).

* All rows that are copied from the "production" tables must at the same
time be deleted,
i e a specific row can/must only exist either in the production table OR the
equivalent archive table.

* If row, about to be moved, already exists in the archive table then it
should simple be overwritten (or ignored) without complaining.

* The job should be set up to be run each night.

This must be a very common scenario - but I can not find any "move
(copy/overwrite & delete) data task" in DTS...
Or is DTS maybe not the way to go...?

Best regards,
Benjamin Tengelin
Congenial Data
Sweden

I have previously done this in 2 ways

1. Using replication. I altered the DELETE stored Proc for my tables and I
only replicated DELETE statements when they affected rows < 3 years only.
This way i did not remove the > 3 year old rows

2. You could setup a duplicate DB. You then do selects from each table in
the source that meet your deleting criteria. You insert these into their
corresponding archive table. You can then remove from the source system.

Very simplistic examples but it should give you food for thought.

--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Benjamin" <benjamin (AT) congenialdata (DOT) se> wrote

Hi Allan,

I have some archiving requirements very similar to the ones mentioned below.
In addition the archiving needs to be done without affecting the online
production db by much.
In case of solution no 2 you proposed below, the db table may get locked up
for a long time while deleting maybe 10 million rows from the source and not
be available to any application trying to access the same db table.
Can you suggest any solution for my additional requirement?

thanks,
Nadeem.


"Allan Mitchell" wrote:

Removing 10 million rows in a hit will hurt yes. Why then not batch it.
You could have the archiving run more frequently but less heavily. For this
table with 10 million rows you may delete only 50k at a time allowing the
system to breathe. It would also mean you may not lock as many rows as
previously and thereby provide greater access to the table

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"dbArchive" <dbArchive (AT) discussions (DOT) microsoft.com> wrote

You will need to set up the INSERT and DELETE statements so that the same
rows are not moved twice.

So your criteria might be that you want to the top 10000 each time in Date
order descending ????

DELETE FROM TABLE WHERE DateField IN (SELECT TOP 10000 DateField FROM TABLE
Order By DateField Desc)

Or something of that nature

You could also set up a loop that keeps a track of where it is and deletes
weeks at a time


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"dbArchive" <dbArchive (AT) discussions (DOT) microsoft.com> wrote