How Do I Extract Few Months/Weeks of Data

Question

How Do I Extract Few Months/Weeks of Data

Like2SQL

SSCommitted

Points: 1662
More actions
October 13, 2014 at 3:07 pm

#297332

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!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

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.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

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]

Like2SQL SSCommitted Points: 1662 More actions · Answer 2

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 4

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.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions