How to take the snap shot of a table in sql server 2008

  • Hi All,

    I need your advice regarding taking snapshot of a table. I have a table named 'Balances'. I need to take the snapshot of this table('Balances') till may 7 2012 data, I need to use this snapshot table to create a report. and again on june 8 2012 i need to take another snapshot of this table and use it to create june report. I want to confirm with you that "CAN WE TAKE THE SNAPSHOT OF A SINGLE TABLE IN SQL SERVER??" I know we can take the snapshot of entire database but not a single table!! Correct me if i am wrong!!

    Thanks for your time!!

    Raj Vardhan

  • As far as I'm aware, you cannot take a table snapshot (but there are rather talented people on here who may prove me wrong). If I were you I'd pull the info into a datamart using ssis to extract the data from the source table and report from that. HTH

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Well, no, not really.

    You can either reconstruct the data from an auditing structure which will hold all deltas, or you can set up a process (say, that runs overnight) that will copy the data from the table into another table with the date of the copy.

    Be forewarned, the second option is a HUGE data-sink.

    What is the end goal you are trying to accomplish? I realize you want daily point in time looks at the data, but what's the final intent? That will help drive some of the solutions to what you're looking for.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks Craig for your response. Our goal is to generate a report by using the snapshot table. we are creating a forecast comparison report which will compare the forecasts given by salesperson each month and we want to show the variance of forecast of months in a single report.

  • Thanks andrew!! Now it is confirmed that we cannot explicitly take the snapshot of a sql table. I will try your method by taking the snapshot data into a data mart....

  • tinnusqlserver (4/30/2012)


    Thanks Craig for your response. Our goal is to generate a report by using the snapshot table. we are creating a forecast comparison report which will compare the forecasts given by salesperson each month and we want to show the variance of forecast of months in a single report.

    Ah, I see. Yeah, the easiest method is as Andrew suggested then and using a datamart for this, which I see you're going to attempt to implement. Good luck. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

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

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