How Do I Extract Few Months/Weeks of Data

  • Like2SQL

    SSCommitted

    Points: 1662

    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!

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    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

    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: 183633

    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: 30414

    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: 286982

    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 6 (of 6 total)

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