July 25, 2011 at 4:46 pm
Hi,
We are currently in the process of making a data archiving plan for our production database. We are trying to list out our tables in the database in order of where the data lies that we need to copy across.
For example, if we are archiving data for 2008 and take a scenario of Customers, Orders and Order_Items we need to first copy across the Customers that were created pre 2008, then the Orders data for 2008 then the Order_Items that are linked to the Orders data that we have archived. So our list would be:
1. Customers
2. Orders
3. Order_Items
etc......
The issue is that our production database has 264 tables and manually going through them all is going to be painful!
I have a script to list out all the foreign keys but that brings back over 400 keys so again this will be painful to go through.
Just wondering if anyone had a script out there that would at least be a good base to work from that would list out the order in which the tables of a database would need to be archived in order to not violate any foreign key relationships and ensure all linked data is present!
Any help appreciated!!
Cheers
JK
July 25, 2011 at 5:34 pm
Hi JK
The joy of archiving data.
I heard you on doing the planning might be a bit of a mission, but if you think determining the order is difficult.
Try to determine why data integrity has gone for a ball of cotton 12 months down the line when you realise the archiving process was badly written.
Take your time with archiving , planning it well will be the best thing you can do for youserlf and any DBA that might take over from you one day.
You are moving data based on Date and FK. This will imply that every table will have a relationship.
Is this a once-off, monthly or continues exercise, can the system handle constant deleting on the clustered indexes etc. Are you creating is_archived flag column to do batch deletes at a quiet period, how do you treat lookup tables , do you archive the entire table as there is no date or do you archive only relevant FK -PK lookups. (Be sure to have a process ont to delete from this one) and so the exceptions start.
Archiving can be your legacy or bane.
Take your time if it's 4 or 40 tables or 400. Plan it properly .
Think about logical categories for your data, static, dynamic , lookup and audit , a blanket approach will have many exceptions to it.
If you don't want to think about it, here is a solution for you archiving.
Create a list of all your tables to archive,
then use dynamic SQL and try and insert the data on your source. use try catch, to insert the data,if it fails. increase the sequence in your "loop" table to 1. then try then next table. if it succeed leave the sequence as 0 if it fails set it to max sequence + 1.
Since it's in a loop it will continue untill all FK's have been inserted in correct order.
You can then use the sequence column to determine a hierarchy. (that is if you data is all 100%)
Cheers
Jannie
July 25, 2011 at 5:51 pm
You can try the script here.
http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply