Archive Database

Hi
I have some questions about archive database in the real word case (now we have only 4 gig of data and start to consider archieve database for the last year)
1)Is it correct to archive just only transaction data? No need for some table such as Customers, Vendors
2)Do we use DTS for this mechanism
3)What is the real practice that you do in normal work
Any suggestion welcome because we have no experience about this. If it is not in this group, please recommend me the right group
Best Regards
James JarupanJames,
archiving of historical data may indeed be done using DTS. The data could be
copied (appended) to a separate database then removed from the current
database, both using ExecuteSQL tasks. If you require the archive data to be
available to users along with current data, you can union it in report
queries. If you want a more sophisticated solution you can use distributed
partitioned views to make the union results updatable.
Personally, I have a bit flag on each record which designates if the record
is live or not. However my databases are in the order of 100MB and we have
very few records which might be archived.
HTH,
Paul Ibison|||you are on the right track.
DTS is a great tool for this.
this is the exact direction we are taking (DTS calling a bunch of sprocs) if
it makes you feel better.
You can write stored procedures if you wish instead
etc etc etc.
if you need more info, let me know.
Cheers
Greg Jackson
PDX, Oregon|||Sorry to interrupt you. I also want to know more about archiving a database
in a real world.
When archiving a database, should we backup data in a large flat table? or
keep data in their table (if database have 20 tables, store data in 20
tables with the same structure)?
Also what format of the file is the best for archiving? SQL database file?
compress or not?
Thanks in advance.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> ¦b¶l¥ó
news:%23px$568TEHA.2580@.TK2MSFTNGP12.phx.gbl ¤¤¼¶¼g...
> you are on the right track.
> DTS is a great tool for this.
> this is the exact direction we are taking (DTS calling a bunch of sprocs)
if
> it makes you feel better.
>
> You can write stored procedures if you wish instead
> etc etc etc.
>
> if you need more info, let me know.
>
> Cheers
>
> Greg Jackson
> PDX, Oregon
>|||it depends on what you want to do with it....
"IF" you want to access the archived data for "Reporting", etc then you may
want to "Transform" it into a more flattened or "Denormalized" structure
(standard for OLAP environments).
taking data from a production OLTP system, Flattening it and then putting it
into an OLAP system is very common, and is referred to as "Extract Transform
Load" or "ETL" operations.
you can find a ton of info on this in Books On Line (BOL) or on Google.
cheers
Greg Jackson
PDX, Oregon|||In fact, I just want to move the historical data out from the production
server because these data are too old for the daily operation and the space
of HD is limited. Also no further operation on these old data.
Bennett
"Jaxon" <GregoryAJackson@.hotmail.com> ¦b¶l¥ó
news:OT1nyWjUEHA.716@.TK2MSFTNGP11.phx.gbl ¤¤¼¶¼g...
> it depends on what you want to do with it....
> "IF" you want to access the archived data for "Reporting", etc then you
may
> want to "Transform" it into a more flattened or "Denormalized" structure
> (standard for OLAP environments).
> taking data from a production OLTP system, Flattening it and then putting
it
> into an OLAP system is very common, and is referred to as "Extract
Transform
> Load" or "ETL" operations.
> you can find a ton of info on this in Books On Line (BOL) or on Google.
>
> cheers
> Greg Jackson
> PDX, Oregon
>