October 13, 2014 at 3:07 pm
We have a database which is becoming too big (lots of historical data). We want to extract a few week/months of data (say 3 months) and maybe export it, extract it or somehow replicate it into a new database which the users can work with. Is it possible to extract say 3 months of data? The database has about 15 tables.
Thanks!
October 13, 2014 at 3:11 pm
Of course it is possible and very simple for anyone that knows anything about SQL queries.
You can extract the data by execution a SELECT statement against each table with a WHERE clause to limit the dates to the range you desire.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 14, 2014 at 7:50 am
Hi thanks Alvin - the tables in question don't have a date column. From what you are saying, I take it SQL keeps track of when a record was inserted? Can you refer me to any sample queries - I did search around but couldn't seem to find what I was looking for.
Also - I was reading up on replication and was wondering if I can extract data X weeks/months old using parameterized filters.
Thanks for your help.
October 14, 2014 at 7:59 am
SQL Server keeps track of every change made to the database in the transaction log. The problem is that you can't use it to query tables based on the dates in it.
How do you know that you have historical data if you don't have a date?
October 14, 2014 at 9:12 am
Do you have any kind of identity column or something that would indicate the order that rows were added in? At least then you would have some kind of basis to archive X percent of the records from each table.
October 14, 2014 at 9:31 am
I see two real issues here:
1. Why do you say that your database is too big? If your queries don't perform well, maybe the fault is in the table design or the queries and not the number of rows. A database has to be designed with scalability in mind. After all, most databases grow.
If this is a drive space issue, I hope you have your data on a SAN volume so your network operations team can allocate more space for you. Again, most databases grow.
2. You want to archive by date but you don't know the date when the rows were created. If you don't have another column that will tell you what you can archive, then there's no real way you can archive. I would suggest putting an EntryDate column on all tables. While this will help you moving forward and do nothing to address your immediate problem, at least you won't have this problem come up again elsewhere.
If you have a column you can key in on to determine what you can archive, then all you have to do is insert the rows to archive into an archive table and then delete what you just archived. I would tend to look at #1 first though.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply