How to do this...?

Question

Post reply

How to do this...?

krypto69

SSChampion

Points: 13604
More actions
December 14, 2009 at 9:56 am

#139985

I have a DB that gets updated throughout the day.
My users need the ability to go back in time and search on data say from the end of last month.
Problem is we don't store any old DB's. So, for example the balance field is updated throughout the day, and never gets archived.
Is there way using snapshots or some other methoed, that would let me users search thru an old copy of the DB?

Viewing 15 posts - 1 through 15 (of 16 total)

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

There are lots of ways to archive data.

Backup & restore to different databases. "MyDatabase_20091130"

Save specific tables to a new name "MyTable_20091130"

Scheduled SSIS package, replication, log shipping .....

Idera's latest backup softwaqre allows you to run queries against the backup file without doing a restore, so if you have backups from the correct dates available, that's another option.

You would need to decide what timeframe will be needed then plan & schedule one of the many methods.

krypto69 SSChampion Points: 13604 More actions · Answer 2

K....I know about those...

was hoping there was something I was missing

Thanks!

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

krypto69 (12/14/2009)
I have a DB that gets updated throughout the day.
My users need the ability to go back in time and search on data say from the end of last month.
Problem is we don't store any old DB's. So, for example the balance field is updated throughout the day, and never gets archived.
Is there way using snapshots or some other methoed, that would let me users search thru an old copy of the DB?

Do you have individual rows somewhere that is responsible for the current value of that "balance field"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 4

You could use snapshots if you have Enterprise edition, but if you need to restore or something happens you'll lose them.

A better solution would be calculate this stuff and store it in the db, or better, in a data warehouse. Maintain some point in time calculations that would be useful in reports.

krypto69 SSChampion Points: 13604 More actions · Answer 5

Jeff Moden (12/14/2009)
krypto69 (12/14/2009)
I have a DB that gets updated throughout the day.
My users need the ability to go back in time and search on data say from the end of last month.
Problem is we don't store any old DB's. So, for example the balance field is updated throughout the day, and never gets archived.
Is there way using snapshots or some other methoed, that would let me users search thru an old copy of the DB?
Do you have individual rows somewhere that is responsible for the current value of that "balance field"?

Most are individual fields, some are calculated fields (ie more then one column)

krypto69 SSChampion Points: 13604 More actions · Answer 6

Steve Jones - Editor (12/14/2009)
You could use snapshots if you have Enterprise edition, but if you need to restore or something happens you'll lose them.
A better solution would be calculate this stuff and store it in the db, or better, in a data warehouse. Maintain some point in time calculations that would be useful in reports.

Almost all of these balance fields are used in several reports.

Does it make more sense to just schedule a report to be saved to a disk, or to build out an entire data warehouse for these 10 -20 fields?

If the answer is data warehouse, then any tips advise appreciated.

I'm also intersted in why I couldn't or wouldn't want to use snapshots? Wouldn't that enable my users to serach historical data (prior balances)?

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 7

This may be too simple for your situatiion but you can archive data rows using a new table and an update trigger.

Basically, when an update is made to a table, the update trigger copies the existing row(s) in question to the new table. This way you can query when the rows were changed.

Hope this helps.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Without capturing the required data at specific points in time for reporting, about the only other suggestion I have is to back out of the data the transactions entered after the given point in time you are querying. It may be a bit complex, but it is all I can think of in this situation.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 9

krypto69 (12/15/2009)
Jeff Moden (12/14/2009)
krypto69 (12/14/2009)
I have a DB that gets updated throughout the day.
My users need the ability to go back in time and search on data say from the end of last month.
Problem is we don't store any old DB's. So, for example the balance field is updated throughout the day, and never gets archived.
Is there way using snapshots or some other methoed, that would let me users search thru an old copy of the DB?
Do you have individual rows somewhere that is responsible for the current value of that "balance field"?
Most are individual fields, some are calculated fields (ie more then one column)

Sorry.... I wasn't clear. Do you have the original data somewhere to recalculate all of these balances?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

krypto69 SSChampion Points: 13604 More actions · Answer 10

No Jeff,

The balance data is updated throughout the day and is a rolling balance.

Do I don't really have a way..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

krypto69 (12/15/2009)
No Jeff,
The balance data is updated throughout the day and is a rolling balance.
Do I don't really have a way..

The balance may be a rolling balance, but if you have the individual transactions available that affect the balance, you should be able to roll that information out back to the approriate date.

krypto69 SSChampion Points: 13604 More actions · Answer 12

I don't really have access to the individual transactions. I know that sounds odd, but this is a core system for us, they do not want it tampered with directly.

So, I was hoping to use snapshots for them to hit against, for the historical data they need. And actually it would only be one or two people that would need to access the snapshot data and report off of.

Keith Moffatt SSC Enthusiast Points: 113 More actions · Answer 13

Really, without getting into backups, the easiest way I can see to do this is the capture the data in a table. If you need to know the value every time it changes, you need a trigger to populate a history table. If you just need a kind of survey of the data, you could schedule a job to select the current values into a history table that runs every x minutes (if once an hour is OK for your needs, that really wouldn't be much extra load on the system at all). A job populating a table is a little more convenient that a report sitting on a drive because you can manipulate the data more easily.

gormanv Newbie Points: 4 More actions · Answer 14

I agree. You need the detail with a time stamp on each record. Then you can filter based on the time stamp date. Do a SUM on the amount column to get your 'as of' balance. There would be no way to do this from a balances table.