Strategies to archive old database data

Question

Post reply

Strategies to archive old database data

amns

SSCrazy

Points: 2628
More actions
October 28, 2014 at 8:19 am

#291976

Hi!
I have the requirement to implement archiving of some database old data.
The database is not used to store files, just table with text/numeric data, some data modification logs.
Which strategies do you usually use to archive old records from a database?
Do you move old/unused data to another similar database in another server?
The data needs to be acessible for read access, even when archived.
Thanks!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

amns SSCrazy Points: 2628 More actions · Answer 1

Do you usually use:

1 - table partitioning? (just suitable for Enterprise version)

2 - move old data to another table in the same database on another filegroup with different schema?

3 - move old data to another database with similar table relations?

If you move old data to another database and table has generated incremental int primary keys, do you keep these values? Or generate new ones?

If old data is needed again to read/modify, do you usually allow to re-move the data for the main database?

How do you evaluate if you need ta add archiving to your database? Number of rows of the table?

Old records not acessed?

Sorry if these questions don't make sense, I'm trying to get some guide in this topic...

Thanks

x SSC-Insane Points: 23660 More actions · Answer 2

I think this is a pretty good topic, hopefully more folks with more experience can pitch in.

I've done some archiving. Pretty much across the board with stuff, I've taken snapshots with the intention of reinserting when the records were deleted and then the folks wanted an "undelete." I've had to deal with archiving routines that have a possible "race condition", where the first part of the procedure copies to the archive table with some criteria, and the next part of the routine uses the same criteria to delete, but this neglects to take into consideration the arrival of new data that can fit this criteria, so watch out for that.

I've just done one today for another "undelete" function, and this one is ok because the main table uses an identity, and the archive table copies this identity value verbatum (to a non-identity column) and then I can use this same column to identify rows to delete in the main table. Reinserting from the archive obviously needs special handling for this one. It would be interesting to hear any downsides to this approach.

The main focus for me is to do the archiving procedure, and then think of ways it could actually lose records, just to avoid any pitfalls in the logic I use to produce the archiving procedure.

amns SSCrazy Points: 2628 More actions · Answer 3

Thanks for sharing your experience!

I think I will use another database with some similar tables to where I will move some data.

If required I will allow to query these archived data for reading purposes, but by default they will not be queried.

If occasionally unarchiving data is needed, I will allow to move data back to original database.

Thanks!